8

So I recently moved jobs - one piece of code I've spotted in our build scripts for new SQL Server installations is below.

IF EXISTS ( SELECT  *
            FROM    [sys].[syslogins]
            WHERE   [name] = N'NT AUTHORITY\SYSTEM' )
    BEGIN
        DROP LOGIN [NT AUTHORITY\SYSTEM];
    END

IF EXISTS ( SELECT  *
            FROM    [sys].[syslogins]
            WHERE   [name] = N'NT SERVICE\SQLWriter' )
    BEGIN
        DROP LOGIN [NT SERVICE\SQLWriter];
    END

IF EXISTS ( SELECT  *
            FROM    [sys].[syslogins]
            WHERE   [name] = N'NT SERVICE\Winmgmt' )
    BEGIN
        DROP LOGIN [NT SERVICE\Winmgmt];
    END
GO

These accounts are created during the SQL Server installation process by default.

Are dropping the above logins recommended? Are there any side effects this could cause? What are these logins used for?

I read Can I delete NT SERVICE\SQLWriter and NT SERVICE\Winmgmt logins? but it just isn't concrete enough - I can see what they're needed for but very little else. Do they need sysadmin access? etc.

As an example (taken from Configure Windows Service Accounts and Permissions):

Local System is a very high-privileged built-in account. It has extensive privileges on the local system and acts as the computer on the network. The actual name of the account is NT AUTHORITY\SYSTEM.

How should I read this? Should it be left with these "high" privileges?

  • See this previous related question. Personally I feel like this is something that should be done only if (a) there is some tangible benefit to doing so and (b) there is proof that side effects won't affect you. – Aaron Bertrand Apr 29 at 14:29
2

Before you downvote, here is formal documentation you can reference for a legitimate reason why these accounts would be scripted as you see: STIG Rule SV-53421r2_rule. These controls are SQL Server version specific, but there are a number of other controls in the more recent versions as well. If you work for an organization that falls under these controls and also adheres to some of the more stringent ones, such as revoking default Grants given to the public role things can get complicated pretty quickly.

As I have some experience in an environment that falls under these controls, I can say that you can disable/drop both the NT SERVICE\SQLWriter and NT SERVICE\Winmgmt accounts outright, but you need to make sure your SQL Server Service is running under an appropriate service account with sufficient OS level permissions, such as a sufficiently locked down Domain Service Account, or even better, standalone or group Managed Service Accounts. Again, OS permissions need to be treated with care as this does venture into house-of-cards territory if you don't perform sufficient testing.

As for the NT AUTHORITY\SYSTEM account, this is NOT an account you want to drop if you are going to be running any Availability Groups. In fact, STIG SV-93835r1_rule mentions that you should keep it with only CONNECT SQL permissions granted by default. In the event that you have an Availability group, this account will also need to have the following, additional permissions:

  • ALTER ANY AVAILABILITY GROUP
  • VIEW SERVER STATE
  • EXECUTE ON [sys].[sp_server_diagnostics]
  • EXECUTE ON [sys].[sp_availability_group_command_internal]

These restrictions can be a royal pain, but there are legitimate reasons you would need to limit their use and permission sets. If you don't fall under these guidelines, do yourself the favor and comment out those steps.

Hope that helps!

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.