Cannot disable NTgroup users using Alter Logins script

  • I am trying to use following command . Looks like it cannot disable NT group uses. All other users are getting disable without any problem except the following one.

    ALTER LOGIN [SUTTER-CHS\SC.IT_Data_Services] disable.

    Here is the error.

    Msg 15151, Level 16, State 1, Line 1

    Cannot alter the login 'SUTTER-CHS\SC.IT_Data_Services', because it does not exist or you do not have permission.

  • Usually, NT domain users are assigned to SQL Servers and databases as parts of groups. The individual login might not be there. Check under the Users tab for the server.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Individual logins are there.

  • use what kind of security?


    [font="Arial"]MCDBA, MCITP (DB Dev, DB Admin), MCSE,MCTS, OCA 10g[/font]

  • double check spelling of domain\username

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • NT has sysadmin rites so u can disable builtin/adminstrator

  • Account has folllowing rights

    SecurityAdmin

    serveradmin

    setupadmin

    sysadmin

    I checked the domain name and it's correct. I can disable all the logins except NT Group Logins.

  • What Login are you using to disable? If you can't 'trump' the NT login, then you don't have sufficient rights, and that would trigger the error message.

    Can YOU login using the ID in question?

    Why are you trying to disable this ID? Would disabling the password not be equally effective?

  • Steve.. It's a AD group account in SQL Server. The reason I am trying to disable these user account because I don't want any user to connect to the server if I am doing any processing.

    DBA, who used to work here had made a process which kills user for a specific time but I don't like the idea.

    Yes , disable the password is a good idea. How can I do that. ?

  • It occurs to me that there's a problem with 'disabling' the password, depending on how the ID is used. IF you have administrative rights to the machine, then you put on your admin hat and change the password on the login ID - however, you have to make sure you aren't breaking anything else.

    Because the ID is using Windows authentication, it's not as simple as simply changing the password within SQL Server (think back to 7.0 days, and earlier...)

    The joys of not thinking things through... However, you could remove the ID, as an administrator, from having rights to SQL Server at all, or setup a 'deny read' on the database directory where the content resides ... lots of squirrelly ways of locking out, but heaven help you if you forget what you did and want to undo it.

    IF you don't have admin rights, then you need to be political about this - read, not a technical discussion, no bias intended - and deal with the sys admins and your joint management to come to an understanding. You'll have to explain the whys and wherefores of what you want to accomplish and get an agreement as to how you will implement all this.

    Not everyone has been burned by NOT establishing proper controls, so you may have to do some educating and preaching as well. Good hunting! (I've read lots and lots of threads on the forums about this, but can't cite anything specifically)

  • From BOL under the ALTER LOGIN help:

    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.

    So, I think your options are either of the following:

    1) Drop the login and when done with your processing, create the login

    2) Deny connect to SQL option for the group:

    DENY CONNECT SQL TO [group]

    GO

    I would probably opt for option 2 myself.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Steve.. Thanks a lot.

    I like your idea of denying connection to sql server. Here is the TSQL I am going to use. I will deny connection for all the users for specific time and then run my process and then after that Grant Connect to all the users.

    DENY CONNECT SQL TO [SUTTER-CHS\SC.IT_Data_Services]

    Thanks you for all your help.

  • balbirsinghsodhi (7/15/2008)


    Steve.. Thanks a lot.

    I like your idea of denying connection to sql server. Here is the TSQL I am going to use. I will deny connection for all the users for specific time and then run my process and then after that Grant Connect to all the users.

    DENY CONNECT SQL TO [SUTTER-CHS\SC.IT_Data_Services]

    Thanks you for all your help.

    Not Steve - but you are welcome 😀

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Sorry about that.

    Thank you Jeffrey.

  • You could try the following SQL to disable the NT group.

    ALTER LOGIN [Domain\NT_Group_Name] DISABLE

    GO

Viewing 15 posts - 1 through 15 (of 17 total)

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