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

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.


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