SQL Server – Server Principals / Logins

 

Background

In this post we will cover some of the principals that are created upon SQL Server Installation.

In some cases full sysadmin permissions are granted.

And, in some cases, through separation of duties, minimal permissions are granted.

 

Server Principals

Matrix

Name SID Type Create Date Usage
 ##MS_PolicyEventProcessingLogin##  0x51D095ECDC6C164C9B6047F5838CAAA0 SQL_LOGIN  [Install Date] Policy Based Management
 ##MS_PolicyTsqlExecutionLogin##  0xA9EEB439F4762546BD90D163703F6DA1 SQL_LOGIN 2014-02-20 20:49:46.837  Policy Based Management
 NT AUTHORITY\SYSTEM  0x010100000000000512000000  WINDOWS_LOGIN  [Install Date]  Local System
NT SERVICE\MSSQLSERVER  0x010600000000000550000000E20F4FE7B15874E48E19026478C2DC9AC307B83E  WINDOWS_LOGIN  [Install Date] Database Engine / Default Instance
NT Service\MSSQL$[Instance-Name]  WINDOWS_LOGIN  [Install Date]  Database Engine / Named Instance
 NT SERVICE\ReportServer  0x010600000000000550000000214401ACF066EA342187301080455260EB684BA2  WINDOWS_LOGIN  [Install Date] Reporting Services / Default Instance
 NT SERVICE\ReportServer$[Instance-Name]  WINDOWS_LOGIN  [Install Date] Reporting Services / Named Instance
NT SERVICE\SQLSERVERAGENT 0x010600000000000550000000DCA88F14B79FD47A992A3D8943F829A726066357  WINDOWS_LOGIN [Install Date] SQL Server Agent
NT SERVICE\SQLAgent$[Instance-Name]  WINDOWS_LOGIN  [Install Date]  SQL Server Agent / Named Instance
 NT SERVICE\SQLWriter  0x010600000000000550000000732B9753646EF90356745CB675C3AA6CD6B4D28B  WINDOWS_LOGIN  [Install Date]  Database Backup
 NT SERVICE\Winmgmt  0x0106000000000005500000005A048DDFF9C7430AB450D4E7477A2172AB4170F4  WINDOWS_LOGIN  [Install Date]  Windows Management

 

 

 

Categories

  1. Policy Based Management
    • ##MS_PolicyEventProcessingLogin##
    • ##MS_PolicyTsqlExecutionLogin##
  2. Local System Account
    • NT AUTHORITY\SYSTEM
  3. NT SERVICE\MSSQLServer
    • Database Engine
  4. NT SERVICE\ReportServer
    • The account specified during setup is provisioned as a member of the RSExecRole database role.
      Link
  5. NT SERVICE\SQLServerAgent
    • SQL Server Agent
      • Link
        The per-service SID of the SQL Server Agent service is provisioned as a Database Engine login. The per-service SID login is a member of the sysadmin fixed server role.
  6. NT Service\SQLWriter
    • SQL Writer Service
      • Link
        The SQL Writer service must run under the Local System account. The SQL Writer service uses the NT Service\SQLWriter login to connect to SQL Server. 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. If neither SQL Server, the system it runs on, nor the host system (in the event of a virtual machine), need to use anything besides Transact-SQL backup, then the SQL Writer service can be safely disabled and the login removed. Note that the SQL Writer service may be invoked by a system or volume level backup, whether the backup is directly snapshot-based or not. Some system backup products use VSS to avoid being blocked by open or locked files. The SQL Writer service needs elevated permissions in SQL Server because in the course of its activities it briefly freezes all I/O for the instance of SQL Server.
  7. NT Service\Winmgmt
    • WMI
      Link
      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.

 

 

References

Microsoft Developer Network

  1. Database Engine > Database Engine Features and Tasks  > Security Center for SQL Server Database Engine and Azure SQL Database
    Link
  2. Database Engine Features and Tasks > Database Engine Instances (SQL Server)  > Manage the Database Engine Services
    Link

 

Blogs

  1. Database Dave
    • Dont delete ##MS_PolicyEventProcessingLogin## or ##MS_PolicyTsqlExecutionLogin##
      Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s