User cannot change his own password without ALTER ANY LOGIN permission - by marek_adamczuk

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 253856 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 1/25/2007 10:22:41 AM
Access Restriction Public


On SQL Server 2000 user was able to change his own password with sp_password procedure. In SQL Server 2005 it doesn't work because sp_password calls ALTER LOGIN which requires ALTER ANY LOGIN permission absolutely without checking, that user chages his own password.
To enable the possibility for ordinary users to change their own passwords you must grant them ALTER ANY LOGIN permission. It is a security risk, because they then are able to change any password including sysadmins!

Tested on Dev Edition on WinXP and Win 2003 Srv Enterprise.
Sign in to post a comment.
Posted by Laurentiu [MSFT] on 1/26/2007 at 2:46 PM
I am closing this case - if you have further questions about this scenario, just post a message on the security forums at: Do not update this thread as it will no longer be monitored.

Thank you
Posted by Laurentiu [MSFT] on 1/26/2007 at 2:43 PM
You probably executed the alter login twice without noticing, which would lead to the second attempt generating an error message because the password has already been changed, so 12345 is no longer a valid old password - 123456 is now the old password. You can check this by attempting to connect with the 123456 password.
Posted by marek_adamczuk on 1/26/2007 at 1:48 AM
Sorry - IT IS TRUE! As I understand that changing password (not reseting) requires both old and new password. If so - the second example is

-- login as admin
create login abc with password = '12345'
execute as login = 'abc'
alter login abc with password = '123456' old_password = '12345'
--- result
Server: Msg 15151, Level 16, State 1, Line 1
Cannot alter the login 'abc', because it does not exist or you do not have permission.
sp_password @loginame = NULL, @old = '12345', @new = '123456'
-- result: FAIL!
Server: Msg 15151, Level 16, State 1, Line 1
Cannot alter the login 'abc', because it does not exist or you do not have permission.

On SQL Server 2000
-- login as sysadmin
exec sp_addlogin 'abc','12345'
-- login as abc
sp_password @Loginame = NULL, @Old = '12345', @New = '123456'
-- IT WORKS! THERE IS the change of behavior between SQL 2000 and SQL 2005!!!

--- but ok, this doesn't work (now specyfing login)
sp_password @Loginame = 'abc', @Old = '123456', @New = '1234567'

OK - so specyfing login is bad thing. But sp_password sets @loginame to suser_sname() ALWAYS when IS NULL and then executing alter login and it doesn't work. So I try on SQL 2005 with null:

alter login null with password = '123456' old_password = '12345'

Of curse - doesn't work!. Incorrect syntax near NULL!
There is NO WAY for non admin user to change his own password! Or I still don't know something...
Posted by Laurentiu [MSFT] on 1/25/2007 at 3:24 PM
That is not true. A user can still change his own password, as mentioned in BOL:

A user cannot, however, reset his password. What you were attempting was a password reset, not a password change. For a password change, a user should provide his old password.

ALTER ANY LOGIN is the permission required for resetting passwords. Resetting has always been a special operation. See:

Also, if you would test your TSQL on SQL Server 2000, you would notice it would also fail to reset the password - only a sysadmin can specify the @loginame parameter and a regular user needs to specify the correct @old password.

There is no change in behavior of sp_password from SQL Server 2000 to SQL Server 2005.