How do I identify all super users in SQL Server?

Question

For a security audit,I need to identify all super users and log every activity performed. Best approach for this?

By "super user" I mean sysadmin, serveradmin, securityadmin, processadmin, and dbcreator.

Answer 1

You can use this code:

SELECT   name,type_desc,is_disabled, 
         (IS_SRVROLEMEMBER ('sysadmin',name)) AS ISSRVROLE
FROM     master.sys.server_principals 
-- WHERE    IS_SRVROLEMEMBER ('sysadmin',name) = 1
ORDER BY name ;

sys.server_principals - Contains a row for every server-level principal

IS_SRVROLEMEMBER - Indicates whether a SQL Server login is a member of the specified server role.

You would also need to check for the sysadmin-equivalent CONTROL SERVER permission for the server principal in sys.server_permissions.

IS_SRVROLEMEMBER can return:

• 0 - login is not a member of role.
• 1 - login is a member of role.
• NULL - role or login is not valid, or you do not have permission to view the role membership.

Answer 2

You can use below code to get the details:

WITH cte_SuperUser (name, role, type_desc)
AS (SELECT
  PRN.name,
  srvrole.name AS [role],
  Prn.Type_Desc
FROM sys.server_role_members membership
INNER JOIN (SELECT
  *
FROM sys.server_principals
WHERE type_desc = 'SERVER_ROLE') srvrole
  ON srvrole.Principal_id = membership.Role_principal_id
RIGHT JOIN sys.server_principals PRN
  ON PRN.Principal_id = membership.member_principal_id
WHERE Prn.Type_Desc NOT IN ('SERVER_ROLE')
AND PRN.is_disabled = 0
UNION ALL
SELECT
  p.[name],
  'ControlServer',
  p.type_desc AS loginType
FROM sys.server_principals p
JOIN sys.server_permissions Sp
  ON p.principal_id = sp.grantee_principal_id
WHERE sp.class = 100
AND sp.[type] = 'CL'
AND state = 'G')
SELECT
  name,
  Type_Desc,
  CASE
    WHEN [public] = 1 THEN 'Y'
    ELSE 'N'
  END AS 'Public',
  CASE
    WHEN [sysadmin] = 1 THEN 'Y'
    ELSE 'N'
  END AS 'SysAdmin',
  CASE
    WHEN [securityadmin] = 1 THEN 'Y'
    ELSE 'N'
  END AS 'SecurityAdmin',
  CASE
    WHEN [serveradmin] = 1 THEN 'Y'
    ELSE 'N'
  END AS 'ServerAdmin',
  CASE
    WHEN [setupadmin] = 1 THEN 'Y'
    ELSE 'N'
  END AS 'SetupAdmin',
  CASE
    WHEN [processadmin] = 1 THEN 'Y'
    ELSE 'N'
  END AS 'ProcessAdmin',
  CASE
    WHEN [diskadmin] = 1 THEN 'Y'
    ELSE 'N'
  END AS 'DiskAdmin',
  CASE
    WHEN [dbcreator] = 1 THEN 'Y'
    ELSE 'N'
  END AS 'DBCreator',
  CASE
    WHEN [bulkadmin] = 1 THEN 'Y'
    ELSE 'N'
  END AS 'BulkAdmin',
  CASE
    WHEN [ControlServer] = 1 THEN 'Y'
    ELSE 'N'
  END AS 'ControlServer'
FROM cte_SuperUser
PIVOT
(
COUNT(role) FOR role IN ([public], [sysadmin], [securityadmin], [serveradmin], [setupadmin],
[processadmin], [diskadmin], [dbcreator], [bulkadmin], [ControlServer])
) AS pvt
WHERE Type_Desc NOT IN ('SERVER_ROLE')
ORDER BY name, type_desc
GO

Answer 3

the following code will give you all the members of SysAdmin on your server

SELECT ServerLogin.name
FROM sys.server_principals ServerRole
JOIN sys.server_role_members RoleMembers
 ON ServerRole.principal_id = RoleMembers.role_principal_id AND ServerRole.name = 'SysAdmin'
join sys.server_principals ServerLogin
 ON ServerLogin.principal_id = RoleMembers.member_principal_id

Answer 4

Logging every activity on a SQL server is not a "cheap" operation.

You can use database audit to track specific kind of operation (those that would required admin privilèges). This way, you won't have to bother about identifying those users at first.

Otherwise, you can use the queries provided in previous answers to track the users that have admin level and then run an extended event or a trace to capture every operation done by those users. Keep it mind that this may affect performance as SQL will have to track and save every transaction (specially if using a trace).

Answer 5

The following script lists logins that are members of the sysadmin server role and those having the CONTROL SERVER permission:

SELECT   
     spr.name as loginName
    ,spr.type_desc
    ,spr.is_disabled
    ,(IS_SRVROLEMEMBER ('sysadmin',spr.name)) AS IS_SYSADMIN
    ,spm.permission_name
    ,spm.state_desc
FROM     
    sys.server_principals spr
    inner join
    sys.server_permissions spm ON spm.grantee_principal_id = spr.principal_id
WHERE    
    spm.permission_name = 'CONTROL SERVER'
    OR
    IS_SRVROLEMEMBER ('sysadmin',spr.name) = 1

Answer 6

For future readers, if you want to automate this for your entire sql server estate then, I would highly recommend using dbatools - powershell based tool to ease your life working with sql server.

For identifying "fixed server roles", you can use Get-DbaDbRoleMember

e.g. Returns all members of the db_owner role in the msdb database on localhost.

Get-DbaDbRoleMember -SqlInstance localhost -Database msdb -Role 'db_owner'

if you want to use multithreading, I blogged on how to multithread dbatools cmdlets using PoshRSJob module.