Home Dashboard Directory Help
Search

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


Status: 

Closed
 as By Design Help for as By Design


0
0
Sign in
to vote
Type: Bug
ID: 253856
Opened: 1/25/2007 10:22:41 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

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.
Details
Sign in to post a comment.
Posted by Microsoft 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: 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 admin
create login abc with password = '12345'
GO
execute as login = 'abc'
GO
alter login abc with password = '123456' old_password = '12345'
GO
--- 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.
GO
sp_password @loginame = NULL, @old = '12345', @new = '123456'
GO
-- 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.
GO


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

--- 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 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.
Sign in to post a workaround.