sysadmin cannot alter login

  • I have an unusual problem.

    I am connected to my production SQL Server and have just added a new trusted login.

    This has been done successfully, however I need to disable the login as it is only to be enabled on an "as required" basis for special conditions.

    However when i use either the management studio or the ALTER LOGIN [login] DISABLE command, i get back the error message:

    Disable Login. failed for Login 'xxx\xxxx'

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

    I have double checked the following:

    I am definitely connected as a system administrator.

    I have the necessary permissions to alter logins.

    I can delete the login.

    I can map and unmap the login to database and grant/deny/revoke permissions on objects

    I can connect to the database using the login and query tables, etc., as appropriate to the way I have set it up.

    But I cannot change the enabled or disabled status of the login.

    Can anyone help?


    ---------------------------------------
    It is by caffeine alone I set my mind in motion.
    It is by the Beans of Java that thoughts acquire speed,
    the hands acquire shaking, the shaking becomes a warning.
    It is by caffeine alone I set my mind in motion.

  • Quick check, does this return any rows?

    select name, type_desc, is_disabled

    from sys.server_principals

    where name = 'xx\xxxx'

  • Yes, one row

    type_desc = WINDOWS_GROUP

    is_disabled = 0


    ---------------------------------------
    It is by caffeine alone I set my mind in motion.
    It is by the Beans of Java that thoughts acquire speed,
    the hands acquire shaking, the shaking becomes a warning.
    It is by caffeine alone I set my mind in motion.

  • cannot disable from the GUI?

  • If I try to disable the login from either the GUI or direct using T-SQL both come back with the same error.


    ---------------------------------------
    It is by caffeine alone I set my mind in motion.
    It is by the Beans of Java that thoughts acquire speed,
    the hands acquire shaking, the shaking becomes a warning.
    It is by caffeine alone I set my mind in motion.

  • You cannot disable windows groups.

    From BOL (TSQL Reference 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.

  • Argh!

    Missed this one, thanks.

    This is going to make administering the logins a pain, lol. :hehe:


    ---------------------------------------
    It is by caffeine alone I set my mind in motion.
    It is by the Beans of Java that thoughts acquire speed,
    the hands acquire shaking, the shaking becomes a warning.
    It is by caffeine alone I set my mind in motion.

  • You may be able to rig something with a login trigger.

  • gklundt (12/1/2010)


    You cannot disable windows groups.

    From BOL (TSQL Reference 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.

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

  • You should still be able to DENY CONNECT to that Group...

    Andreas

    ---------------------------------------------------
    MVP SQL Server
    Microsoft Certified Master SQL Server 2008
    Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.insidesql.org/blogs/andreaswolter
    www.andreas-wolter.com

  • Please note: 3 year old thread.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • *lol*

    :blink:

    ...

    (wasn't me... :ermm:)

    Andreas

    ---------------------------------------------------
    MVP SQL Server
    Microsoft Certified Master SQL Server 2008
    Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.insidesql.org/blogs/andreaswolter
    www.andreas-wolter.com

  • FYI : If you use DENY then whoever are into that group lost SQL Server level access for that particular SQL Server even though they are in some other group which has DB reader etc.. permission on some database on same server . and You end up with error 18456....

  • FYI : If you use DENY then whoever are into that group lost SQL Server level access for that particular SQL Server even though they are in some other group which has DB reader etc.. permission on some database on same server . and You end up with error 18456....

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply