SQL Server security report to show sysadmins and database owners

By: Eli Leiba   |   Updated: 2016-11-14   |   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.

About the author

Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips