Post reply

Proper use of ALTER LOGIN to deny user(s) from Windows group access to SQL Server

  • March 9, 2011 at 5:14 pm

    #389016

    According to BOL, sp_denylogin can be used to prevent a Windows user or Windows group from connecting to a SQL Server instance. It also says that this command is deprecated and to use ALTER LOGIN instead.

    When I check ALTER LOGIN in BOL, it says "You cannot use ALTER LOGIN with the DISABLE arguement to deny access to a Windows group."

    BOL doesn't have an example of how to use ALTER LOGIN to prevent a Windows user or Windows group from connecting to a SQL Server instance, so can anybody share with me the proper ALTER LOGIN syntax to perform this task?

  • March 28, 2011 at 2:36 pm

    #1304579

    the correct syntax to disable a login is:

    alter login [domain\username] disable;

    you just cannot disable windows group logins, only individual users.

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • November 19, 2014 at 10:57 am

    #1759584

    So there's just no way to disable a Windows Group login?

  • November 19, 2014 at 2:07 pm

    #1759642

    robin.pryor (11/19/2014)

    So there's just no way to disable a Windows Group login?

    only deny, but be careful here. Please see this link

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

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

  • January 5, 2015 at 5:02 pm

    #1767919

    There is a tried and true method for disabling a win group in sql. Now don't tell anyone, cause this is a secret I've kept close for a long time.

    Drop Login [Domain\WinGroup]

    Seriously, if you want to deny it then drop it and be done.

    I understand it may be a situation where you don't want to disable permenantly, but you may have to manage the process in that case.

    You'd have to script it out and just run that code whenever you wanted to bring it back.

    If you don't want to do that, then a login trigger may be the trick. Just rollback if it's in that group, but I'd have to test hat. I don't know if the EVENTDATA() function will pick up the group name.

    Other than that, y, deny connect rights.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

Viewing 5 posts - 1 through 4 (of 4 total)

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