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?
Best Answer
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
andNT 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 onlyCONNECT 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: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!