SQL Server security report to show sysadmins and database owners

By: Eli Leiba | Comments | Related: > Security

Problem

You want to create a T-SQL tool to monitor and report which accounts have sysadmin or database owner privileges on a given SQL Server instance. How can this be accomplished using T-SQL?

Solution

Knowing who has sysadmin level rights or database owner rights for SQL Server is a critical DBA task. We will create a simple solution that provides you a report so you can quickly see what accounts have these elevated permissions.

Things we want to track:

At the server level, the sysadmin and securityadmin roles should be monitored since any member of the sysadmin role can do anything within SQL Server.
The securityadmin role should also be regularly monitored because a member of that role has the potential to create a login with equivalent rights as member of the sysadmin role.
The members of the db_owner role within a database should also be monitored since they can do anything within the database.

The solution shown here is to create a multi-statement table function that queries the administrative views. This is done in order to extract all the information needed to solve this problem.

The administrative views queried are:

sys.server_principals
sys.server_role_members (for the server level administrative roles)
sys.database
sys.database_principals
sys.database_role_members (for the database level db_owner role)

T-SQL Function

use master
GO
 
CREATE FUNCTION dbo.WhoIsSQLAdminOrDbOwner ()
RETURNS @RtnValue TABLE (
 nr INT IDENTITY(1, 1)
 ,SQL_Level VARCHAR(10)
 ,Role_or_DB_name SYSNAME
 ,Login_Or_User_Name SYSNAME
 )
AS
BEGIN
 INSERT INTO @RtnValue (
  SQL_Level
  ,Role_or_DB_name
  ,Login_Or_User_Name
  )
 SELECT 'SERVER' AS SQL_level
  ,R.NAME AS 'Role'
  ,L.NAME AS 'Login'
 FROM sys.server_principals L
  ,sys.server_role_members rm
  ,sys.server_principals r
 WHERE L.principal_id = RM.member_principal_id
  AND R.principal_id = RM.role_principal_id
  AND R.NAME IN (
   'sysadmin'
   ,'securityadmin'
   )
 ORDER BY R.NAME
  ,L.NAME;

 INSERT INTO @RtnValue (
  SQL_Level
  ,Role_or_DB_name
  ,Login_Or_User_Name
  )
 SELECT 'DATABASE'
  ,d.NAME AS dbName
  ,U.NAME AS 'User'
 FROM sys.database_principals U
  ,sys.database_role_members RM
  ,sys.database_principals R
  ,sys.databases D
  ,sys.server_principals s
 WHERE U.principal_id = RM.member_principal_id
  AND R.principal_id = RM.role_principal_id
  AND R.NAME = 'db_owner'
  AND D.owner_sid = s.sid
  AND s.sid = u.sid;

 RETURN
END
GO

Example Execution and Output

use master
GO

SELECT *
FROM dbo.WhoIsSQLAdminOrDbOwner() AS t;
GO

And the sample results are as follows. Note that you can easily distinguish between server and database administrative roles.

  
nr SQL_Level Role_Or_DB_Name   Login_Or_User_Name
-- --------- ----------------  -------------------
1  SERVER    sysadmin          NET\tazgz
2  SERVER    sysadmin          NT SERVICE\MSSQL$SQL2K12
3  SERVER    sysadmin          NT SERVICE\SQLAgent$SQL2K12
4  SERVER    sysadmin          NT SERVICE\SQLWriter
5  SERVER    sysadmin          NT SERVICE\Winmgmt
6  SERVER    sysadmin          sa
7  DATABASE  Northwind         dbo
8  DATABASE  test              dbo

Next Steps

The function was tested with SQL Server 2012 and 2014 Developer Editions.
The query usage of the administrative views can be replaced by calls to the old sp_helpsrvrolemember and sp_helprolemember system stored procedure for older SQL Server versions of SQL Server.
Consider creating a job that regularly executes this function and reports the results to the IT Security department. I would advise scheduling it on a weekly basis if not more frequently.
Check out these other Security tips.