Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

So we can do Windows Login or Mixed Mode, but can we configure SQL Server to only use internal logins and to block all Windows Logins?

Is the only solution to add all potential Windows logins and set them to restricted privileges as a preventative (or reactive) procedure?

share|improve this question
up vote 5 down vote accepted

It cannot be disabled completely, for two reasons:

  • On install, logins are provisioned for NT AUTHORITY\SYSTEM, NT SERVICE\SQLSERVERAGENT (or a group containing the SQL Agent service account), and NT SERVICE\MSSQLSERVER (or a group containing the SQL database engine service account). These are sysadmin-level logins that need to be available for SQL Server to function correctly.

    While a quick test revealed that deleting all three of those logins only prevented SQL Agent from restarting (the database engine came up fine), I'm sure there are other functions that rely on the other two logins... they were created by default for a reason, so I wouldn't mess around with them. (FYI if you test this yourself: the Drop & Create scripting option for a login in SSMS doesn't script server role membership.)

  • In single-user mode, local administrators are automatically granted sysadmin-level privileges regardless of whether or not there is a login created that "contains" those users. This is a coat hanger for when you've locked your keys in the car.

As mentioned in the other answer, only explicitly-created Windows logins will have access to connect (my original comment was incorrect) -- removing all user-created Windows logins is sufficient to prevent access.

If you need to go a step farther and prevent Windows logins from being created, here's a starting point (Policy-Based Management, at least on 2008, doesn't support preventing this as it happens):

CREATE TRIGGER trg_PreventWindowsLogins


    IF (EVENTDATA().exist('/EVENT_INSTANCE[1]/LoginType[1]/text()[1] eq "Windows (NT) Login"') = 1)
        RAISERROR(N'Not allowed to create Windows logins!', 16, 1);


Of course, anyone with enough permissions could defeat this, but that's a separate issue...

share|improve this answer

As far as I'm aware it is not possible to disable Windows authentication on SQL Server 2008 + R2:

The only Windows accounts that will have access are those that are explicitly added as logins (or are members of a group which has a login).

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.