April 1, 2011 at 1:05 pm
I need a user to be able to Alter the login of another user. This is the command from Profiler that is failing:
ALTER LOGIN User1 WITH PASSWORD = '******', CHECK_POLICY=OFF
I ran the below command and it still fails. Not sure what I should be doing exactly.
Grant ALTER ON Login:: User1 To User2
April 1, 2011 at 1:58 pm
Ok I've been digging into this a little more. I guess there is a difference from changing passwords and password resets. If I change a password without providing the old password it's a password reset. Admins can do this but the executing user is not one so I assume that's why it's failing.
ALTER LOGIN User1WITH PASSWORD = '******', CHECK_POLICY=OFF
Is there a way to grant the ability to reset a password to a non-admin user?
April 1, 2011 at 2:13 pm
Well to do as your command is showing "CHECK_POLICY=OFF", you will need to also grant the login CONTROL SERVER permissions. This should allow the login to set MUST_CHANGE, CHECK_POLICY, or CHECK_EXPIRATION properties of another SQL login.
Source (Check the "Permissions" section near the end.)
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 1, 2011 at 2:23 pm
Okay I think I did try that. Is the command this:
GRANT CONTROL ON LOGIN:: [User1] TO [User2]
Yeah even if I do apply that I still can't reset the password.
USE master
GO
ALTER LOGIN USER1 WITH PASSWORD = 'test', CHECK_POLICY=OFF
Msg 15151, Level 16, State 1, Line 1
Cannot alter the login 'User1', because it does not exist or you do not have permission.
April 1, 2011 at 2:35 pm
You would need to execute this command for the login that needs the permission, which I take it would be User2:
GRANT CONTROL SERVER TO [User2]
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 1, 2011 at 2:49 pm
Yes that worked. I had the wrong command. Not sure what the other command did.
Thank you for the reply and solving my issue. Thanks again!
April 1, 2011 at 3:13 pm
To give fair warning on what permission you just gave to that login, CONTROL SERVER is the equivalent of having sysadmin role.
Brian Kelly wrote up a good article on SSC that has some info on it: here AND here
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 4, 2011 at 6:23 am
That's exactly what I'm trying to avoid. This is a third party application in which they grant all users SysAdmin access. I'm trying to lock them down so they have only the permissions they need.
Is there any way to allow a user to reset a password without "Control Server/Sysadmin" Permissions?
April 4, 2011 at 8:05 am
AVB (4/4/2011)Is there any way to allow a user to reset a password without "Control Server/Sysadmin" Permissions?
Well I did not catch this but if you try the command without the command CHECK_POLICY=OFF it might work for you without the CONTROL SERVER permission. From what I have read is the CONTROL SERVER is required for doing the password policy settings.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
May 23, 2011 at 9:46 am
Shawn,
Thanks for the help on this. I can't change how the application works so I can't rewrite that "Alter Login" command. For now if I want to change passwords within that application I'm going to have to grant that permission. I'm just going to do it on an as needed basis then revoke it.
I'm running into more issues where the users need sysadmin access again but I'll post in another thread for those questions. Thanks again.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply