Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


SQL Server security report to show sysadmins and database owners

By:   |   Updated: 2016-11-14   |   Comments   |   Related: More > Security


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?


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
 nr INT IDENTITY(1, 1)
 ,SQL_Level VARCHAR(10)
 ,Role_or_DB_name SYSNAME
 ,Login_Or_User_Name SYSNAME
 INSERT INTO @RtnValue (
  ,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

 INSERT INTO @RtnValue (
  ,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;


Example Execution and Output

use master

FROM dbo.WhoIsSQLAdminOrDbOwner() AS t;

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.

Last Updated: 2016-11-14

get scripts

next tip button

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

View all my tips
Related Resources

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Learn more about SQL Server tools