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

  • 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?

  • 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]

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

  • 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" 😉

  • 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