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?


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


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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