SQL Server Default Sysadmin

We discussed about service account and per-service SID used by SQL Server and other components in our last post. During SQL Server installation, the process also automatically created a few logins and some of them are assigned with sysadmin fixed server role.

Member of sysadmin fixed server role can perform any action in SQL Server. It is the most privileged role in SQL Server. Thorough consideration should be made before granting any login with this role.

This query returns a list of sysadmin fixed role,

SELECT 
 r.name AS [role], 
 m.name AS [member]
FROM sys.server_principals r
INNER JOIN sys.server_role_members s
 ON s.role_principal_id = r.principal_id
INNER JOIN sys.server_principals m
 ON m.principal_id = s.member_principal_id
WHERE r.name = 'sysadmin';

Here are the accounts which are granted with sysadmin role by default in different SQL Server version,

SQL Server 2005
- sa
- NT AUTHORITY\SYSTEM
- BUILTIN\Administrators
- SQLServerMSSQLUser$ComputerName$InstanceName Windows group
- SQLServerSQLAgentUser$ComputerName$InstanceName Windows group

SQL Server 2008/2008 R2
- sa
- NT AUTHORITY\SYSTEM
- Windows user account assigned as sysadmin during installation
- Service account running SQL Server service - or - SQL Server per-service SID (refer to SQL Server 2012 SID login below) if installed on Windows Server 2008 or Windows Vista, and later
- Service account running SQL Agent service - or - SQL Agent per-service SID (refer to SQL Server 2012 SID login below) if installed on Windows Server 2008 or Windows Vista, and later

SQL Server 2012
- sa
- Windows user account assigned as sysadmin during installation
- SQL Server per-service SID login, NT Service\MSSQLSERVER or NT Service\MSSQL$InstanceName
- SQL Agent per-service SID login, NT Service\SQLSERVERAGENT or NT Service\MSSQLAGENT$InstanceName
- SQL Writer per-service SID login, NT Service\SQLWRITER
- SQL WMI account, NT Service\Winmgmt

Notes: sa login is disabled in Windows Authentication Mode.

The default accounts granted with sysadmin fixed server role tighten with lesser privilege account on more recent SQL Server version. BUILTIN/Administrators local Windows group is no longer included in sysadmin role as default since SQL Server 2008 to allow access privilege separation between Windows Administrator and SQL Server Administrator. As mentioned in the previous post, additional security enhancements added in Windows Vista and Windows Server 2008 allow SQL Server 2008/2008 R2 to take advantage of per-service SID to further isolate the service resource accessibility and privilege isolation.

In SQL Server 2012, the powerful built in NT AUTHORITY\SYSTEM account is no longer granted with sysadmin privilege as default. SQL Server 2012 added additional per-service SID for VSS writer and WMI services with sysadmin role.

I hope this post give you a better picture on which accounts that have been granted or removed sysadmin fixed server role by default on different SQL Server version.