Description

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.

Assign To

I am closing this case - if you have further questions about this scenario, just post a message on the security forums at: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1. Do not update this thread as it will no longer be monitored.

Thank you

Posted by Microsoft 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 admincreate login abc with password = '12345'GOexecute as login = 'abc'GOalter login abc with password = '123456' old_password = '12345'GO--- resultServer: Msg 15151, Level 16, State 1, Line 1Cannot alter the login 'abc', because it does not exist or you do not have permission.GOsp_password @loginame = NULL, @old = '12345', @new = '123456'GO-- result: FAIL!Server: Msg 15151, Level 16, State 1, Line 1Cannot alter the login 'abc', because it does not exist or you do not have permission.GO

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 Microsoft on 1/25/2007 at 3:24 PM

That is not true. A user can still change his own password, as mentioned in BOL:

http://msdn2.microsoft.com/en-us/library/ms174428.aspx

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:

http://msdn2.microsoft.com/en-us/library/aa238870(SQL.80).aspx

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.