Post reply

sysadmin cannot alter login

  • November 29, 2010 at 2:38 am

    #231932

    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.

  • November 29, 2010 at 11:01 am

    #1256371

    Quick check, does this return any rows?

    select name, type_desc, is_disabled

    from sys.server_principals

    where name = 'xx\xxxx'

  • December 1, 2010 at 1:46 am

    #1257194

    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.

  • December 1, 2010 at 2:19 am

    #1257201

    cannot disable from the GUI?

  • December 1, 2010 at 2:29 am

    #1257210

    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.

  • December 1, 2010 at 6:15 pm

    #1257694

    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.

  • December 2, 2010 at 12:58 am

    #1257743

    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.

  • December 2, 2010 at 6:47 pm

    #1258179

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

  • October 7, 2013 at 11:59 am

    #1656461

    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

  • October 7, 2013 at 1:40 pm

    #1656508

    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

  • October 7, 2013 at 1:50 pm

    #1656513

    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

  • October 7, 2013 at 1:57 pm

    #1656517

    *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

  • October 10, 2013 at 12:34 pm

    #1657683

    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....

  • October 10, 2013 at 12:35 pm

    #1657684

    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