none
Need help : Msg 15151, Level 16, State 1, Line 1 - CANNOT ALTER LOGIN

    Question

  •  

    Hello Ladies and Gents,

     

    I've got such an issue with granting permissionts to login, to be able to change password and manage another 4 logins.

    Let's say I have Domain Group which has permissions to manage some databases on my SQL server. I would like to grant permissions to this group to be able to alter 3 more SQL logins.

     

     

    I've used this script to do that (under my sysadmin privs)

     

    use [master]

    GO

    GRANT ALTER ON LOGIN::[webbi] TO [GROUPHC\GER_hv_git_web_team]

    GO

    use [master]

    GO

    GRANT CONTROL ON LOGIN::[webbi] TO [GROUPHC\GER_hv_git_web_team]

    GO

    use [master]

    GO

    GRANT VIEW DEFINITION ON LOGIN::[webbi] TO [GROUPHC\GER_hv_git_web_team]

    GO

     

    So I've granted permissions to GROUP GROUPHC\GER_hv_git_web_team to ALTER sql login [webbi].

     

    But when I'm trying somehow to alter this login (connected as member of group 'GROUPHC\GER_hv_git_web_team'. I'm receiving this error message.

     

    Msg 15151, Level 16, State 1, Line 1

    Cannot alter the login 'webbi', because it does not exist or you do not have permission.

     

     

    What should I do then? It was the same also when I've GRANTED  'ALTER ANY LOGIN' to this GROUP.

     

    Have I forgot to setup something?

     

    Version of SQL SErver is : SQL 2005 Standard 32bit with SP2.

    Server is running several instances with multiple databases.

     

    Thanks for answers.


    Best Regards

     

    Patrik

    Friday, June 27, 2008 10:13 AM

Answers

All replies

  • Noone can help? :-( Is this question understandable? I do not want to put the group to SYSADMIN role, to make this work? Is there any other procedure? To grant permissions on same system SPs? Or something? Please help.

     

    Friday, June 27, 2008 12:40 PM
  • can you share the Alter_login script you are trying to execute?

    Monday, June 30, 2008 11:24 AM
    Moderator
  • this might just help you on Alter Login

     

     

    You cannot use ALTER_LOGIN with the DISABLE argument to deny access to a Windows group. For example, ALTER_LOGIN [domain\group] DISABLE will return the following error message:

    "Msg 15151, Level 16, State 1, Line 1

    "Cannot alter the login 'Domain\Group', because it does not exist or you do not have permission."

    This is by design.

    Requires ALTER ANY LOGIN permission.

    If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission.

    If the login that is being altered is a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission when making the following changes:

    • Resetting the password without supplying the old password.
    • Enabling MUST_CHANGE, CHECK_POLICY, or CHECK_EXPIRATION.
    • Changing the login name.
    • Enabling or disabling the login.
    • Mapping the login to a different credential.

    A principal can change the password, default language, and default database for its own login.

    Monday, June 30, 2008 11:37 AM
    Moderator
  • So If I understand it well, what you are going to tell me is, that I'm not able to grant a permissions to Domain Group to be able to ALTER login. What I can do is to GRANT ALTER ANY LOGIN to this group? This is not really good solution. There are 100 logins created. I do not want to let this group to manage all my logins. I would like to give them posibility to Alter only those 3 or 4 logins which are created for their databases. Is there another way?

     

    Monday, June 30, 2008 2:18 PM
  • NO, that 1st part was reffered to DISABLE argument and not to Group as you are thinking.

     

     

    Tuesday, July 01, 2008 4:22 AM
    Moderator
  •  

    go through this, see the permission part, which i already copy pasted here

     

    http://msdn.microsoft.com/en-us/library/ms189828.aspx

    Tuesday, July 01, 2008 4:26 AM
    Moderator
  • Thanks for answers but still don't understand or I don't understand you!

    I'm not Disabling accounts, so doesn't understand a point with DISABLE.

     

    Once more again :

     

    - I'm sysadmin of Server

    - I have two accounts (SQL Logins not Domain Group / Not confusing)

    - I have first account which is called "LoginAllowedToChangePWD"

    - I have second account which is called "SlaveLogin"

    - And what I want to do is to GRANT ALTER ON LOGIN "SlaveLogin" TO "LoginAllowedToChangePWD"

     

    What I've done first was this :

     

    use [master]

    GO

    GRANT ALTER ON LOGIN::[slavelogin] TO [LoginAllowedToChangePWD]

    GO

    use [master]

    GO

    GRANT CONTROL ON LOGIN::[slavelogin] TO [LoginAllowedToChangePWD]

    GO

    use [master]

    GO

    GRANT IMPERSONATE ON LOGIN::[slavelogin] TO [LoginAllowedToChangePWD]

    GO

    use [master]

    GO

    GRANT VIEW DEFINITION ON LOGIN::[slavelogin] TO [LoginAllowedToChangePWD]

    GO

     

    THEN I'M GOING TO LOGIN WITH SQL LOGIN CALLED "LOGINALLOWEDTOCHANGEPWD"

     

    - Ohh, nice I can see my own login

    - Ohh, nice I can see login called "SlaveLogin"

    - Ohh, wha'ts really nice I can change "SlaveLogin" mappings

    - Ohh s**t, I can't change "SlaveLogin" Password

     

    USE [master]

    GO

    ALTER LOGIN [slavelogin] WITH PASSWORD=N'NewPassw0rd' OLD_PASSWORD=N'pwd'

    GO

     

    And answer is :

    Msg 15151, Level 16, State 1, Line 1

    Cannot alter the login 'slavelogin', because it does not exist or you do not have permission.

     

    So I'm trying to go through your posts :

     

    I'm reading something about this and domain group.

     

    Requires ALTER ANY LOGIN permission.

    If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission.

    If the login that is being altered is a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission when making the following changes:

  • Resetting the password without supplying the old password. (I'm supplying old Password)
  • Enabling MUST_CHANGE, CHECK_POLICY, or CHECK_EXPIRATION. (I'm not changin it)
  • Changing the login name. (I'm not changing the name of the login)
  • Enabling or disabling the login. (Not at all)
  • Mapping the login to a different credential (NO)

     

     

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------

     

    So I'm going to logon as SysAdmin again.

     

    And I'm running this script :

    GRANT ALTER ANY LOGIN TO [LoginAllowedToChangePWD]

    GRANT ALTER ANY CREDENTIAL TO [LoginAllowedToChangePWD]

    GRANT CONTROL SERVER TO [LoginAllowedToChangePWD]

     

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------

     

    Again connecting as "LoginAllowedToChangePWD" ...

    Again trying to change password, with providing old one!

     

    USE [master]

    GO

    ALTER LOGIN [slavelogin] WITH PASSWORD=N'NewPassword123' OLD_PASSWORD=N'pwd'

    GO

     

    Answer is!

     

    Msg 15151, Level 16, State 1, Line 1

    Cannot alter the login 'slavelogin', because it does not exist or you do not have permission.

     

     

     

    So I'm asking you last time, is this possible, do I need to GRANT any other special permissions to login called "LoginAllowedToChangePWD", or is this piece of *** just so designed?

     

    So tell me is there another way than to use this :

    EXEC master..sp_addsrvrolemember @loginame = N'LoginAllowedToChangePWD', @rolename = N'sysadmin'

    GO

     

    Thanks

Tuesday, July 01, 2008 7:45 AM
  • check the password you are entering in old password.

    Just tried all the scenario you have mentioned.

     

    I'm getting the same error when and only when I'm entering wrong password in Old password

     

    Tuesday, July 01, 2008 8:39 AM
    Moderator
  •  

    Thanks man, this was working. My collegue was playing also with this a little bit and he changed the password, whithout any notification. Now it seems to be working.

     

    I'll try this with domain group.

     

    Thanks

    Tuesday, July 01, 2008 9:11 AM
  • check the password you are entering in old password.

    Just tried all the scenario you have mentioned.

     

    I'm getting the same error when and only when I'm entering wrong password in Old password

     

    Well, how does SQL Manager utility change the password of a user without knowing the old password? To me that error should not be displayed!!! So it is a bug!!!

    Is there an option to force it to happen without knowing the old password?
    Wednesday, April 01, 2009 11:17 PM
  • If you are an administrator, you are not required to specify the old password - this is called a password reset.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, April 03, 2009 12:00 AM
    Moderator