Which SQL Server/Windows logins require the sysadmin role?

Question

I'm setting up a new SQL Server instance. Which of these SQL Server/Windows logins should have the sysadmin role?

• NT AUTHORITY\SYSTEM
• NT SERVICE\SQLWriter
• NT SERVICE\Winmgmt
• NT SERVICE\MSSQLSERVER
• NT SERVICE\SQLSERVERAGENT

Answer 1

I'm not sure about NT AUTHORITY\SYSTEM. But according to the SQL Server documentation the others are added to the sysadmin role by SQL Server Setup, so I'm guessing it is required.

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-2016#DE_Prov

Answer 2

The NT Service Accounts

• NT SERVICE\SQLWriter
• NT SERVICE\Winmgmt
• NT SERVICE\MSSQLSERVER NT
• NT SERVICE\SQLSERVERAGENT

What

In a Q and A similar to yours, an answer by by Rick Byham, Microsoft explains what these NT SERVICE accounts are

No, they can't be selected in the list of available built-in accounts, local accounts or domain accounts. This is because they are services, not accounts. They have a security identifier (SID) in Windows, but Windows knows they aren't real users. Windows can authenticate them, but they don't have passwords that any human can use. If you run lusrmgr.msc and look at the groups, you will see groups like SQLServerMSSQLUser$computername$MSSQLSERVER and NT SERVICE\MSSQLSERVER is a member of the group.

They are a method for authentication, but cannot be used by users as they are not real logins.

---

NT SERVICE\MSSQLSERVER and NT SERVICE\SQLSERVERAGENT

Details on the authentication for NT SERVICE\MSSQLSERVER & NT SERVICE\SQLSERVERAGENT

By removing the service SID logins or by removing them from the sysadmin server role, problems can result for various components of SQL Server that connect to SQL Server Database Engine. Some problems include the following:

• SQL Server Agent cannot start or connect to a SQL Server service

• SQL Server Setup programs encounters problems (link removed)

Do not remove these logins or take away the sysadmin privileges.

---

NT SERVICE\SQLWriter

This account authenticates the SQL Writer Service and

...must be running when the Volume Shadow Copy Service (VSS) application requests a backup or restore....

In the same post, on removing / disabling this login:

...Using the NT Service\SQLWriter login allows the SQL Writer process to run at a lower privilege level in an account designated as no login, which limits vulnerability. If the SQL Writer service is disabled, then any utility which in relies on VSS snapshots, such as System Center Data Protection Manager, as well as some other 3rd-party products, would be broken, or worse, at risk of taking backups of databases which were not consistent....

Source

In theory if you will never use VSS you could remove the login. In my opinion you should not remove it.

---

NT SERVICE\winmgmt

WMI must be able to connect to the database engine, but could theoretically be restricted more.

Windows Management Instrumentation (WMI) must be able to connect to the Database Engine. To support this, the per-service SID of the Windows WMI provider (NT SERVICE\winmgmt) is provisioned in the Database Engine The SQL WMI provider requires the following permissions:

• Membership in the db_ddladmin or db_owner fixed database roles in the msdb database.

• CREATE DDL EVENT NOTIFICATION permission in the server.

• CREATE TRACE EVENT NOTIFICATION permission in the Database Engine.

• VIEW ANY DATABASE server-level permission.

• SQL Server setup creates a SQL WMI namespace and grants read
  permission to the SQL Server Agent service-SID.

Source

You could restrict these permissions further by revoking sysadmin and granting above permissions.

---

The NT Authority account

• NT AUTHORITY\SYSTEM

This AUTHORITY\SYSTEM is also an SID that relates to the LocalSystem.

...It (LocalSystem) has extensive privileges on the local computer, and acts as the computer on the network. Its token includes the NT AUTHORITY\SYSTEM and BUILTIN\Administrators SIDs; these accounts have access to most system objects...

Source

On an older post by microsoft on this SID.

The NT AUTHORITY\SYSTEM account is also granted a SQL Server login. The NT AUTHORITY\SYSTEM account is provisioned in the SYSADMIN fixed server role. Do not delete this account or remove it from the SYSADMIN fixed server role. The NTAUTHORITY\SYSTEM account is used by Microsoft Update and by Microsoft SMS to apply service packs and hotfixes to a SQL Server 2005 installation. The NTAUTHORITY\SYSTEM account is also used by the SQL Writer Service.

Telling you not to remove this NT AUTHORITY\SYSTEM 'account'