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_role_members (for the server level administrative roles)
- sys.database_role_members (for the database level db_owner role)
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
- 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
About the author
View all my tips