Quick Permissions Audit

A permissions audit could be in order.Whether it is for a client, an audit, or just for good housekeeping, DBAs will often need to figure out who has access to what.  In addition, they may need to know by what means people have access within your databases. Enter the permissions audit.

When that need arises, it is frequently adequate to just perform a quick audit and then dive deeper if the quick audit shows anything questionable. Today, I am sharing a means to perform this quick audit. I will start with the logins (server principals), peek at the users (database principals), then the server roles, and wrapping it up with the database roles. What I don’t do is dive into the windows group memberships.  So if a windows group exists within SQL Server as a login, I do not try to figure out who is a member of that role.  That can be obtained, but is a separate discussion.

So, in the vein of a quick permissions audit, I will quickly get to the script to help perform this permissions audit.

SET NOCOUNT ON;
GO
 
DECLARE @DBList VARCHAR(1000) = 'master,TestDB,TrainingDB, AdventureWorks2014'
/* delimited list of databases */
DECLARE @DBToQuery sysname;
DECLARE @SQL VARCHAR(4000);
 
 
DECLARE @UsersorGroups VARCHAR(1024) = 'Phantom, Gargouille, TestUser'
/* comma delimited list of groups or users to check */
 
 
CREATE TABLE #Principals 
( UserorGroup sysname )  
INSERT INTO #Principals (UserorGroup)
     SELECT Item   
           FROM DBA.dbo.DelimitedSplit8K(@UsersorGroups,',')  
 
CREATE TABLE #DBs 
( DBName sysname )  
INSERT INTO #DBs (DBName)
     SELECT Item   
           FROM DBA.dbo.DelimitedSplit8K(@DBList,',')  
 
CREATE TABLE #PermsAudit (DBName sysname,ServerRole VARCHAR(128), PrincipalName sysname,PrincipalPerms VARCHAR(128)
							,ServerRolePermissions VARCHAR(2048),DBRolePermissions VARCHAR(2048))
DECLARE getperms CURSOR
  LOCAL STATIC FORWARD_ONLY READ_ONLY
     FOR
	 SELECT d.name
		FROM sys.databases d
			INNER JOIN #DBs td
				ON d.name = td.DBName;
OPEN getperms;
 
FETCH NEXT FROM getperms INTO @DBToQuery;
 
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'USE ' + @DBtoQuery +';' + CHAR(10) +
'SELECT DB_NAME() AS DBName,roleprin.name AS ServerRole, pr.name AS PrincipalName, sp.permission_name AS PrincipalPerms
	,(SELECT 
		STUFF( 
			(SELECT '', <'' + sp.permission_name + ''>'' 
				FROM sys.server_permissions sp
				WHERE roleprin.principal_id = sp.grantee_principal_id
				ORDER BY sp.permission_name 
				FOR XML PATH(''''), type 
				).value(''(./text())[1]'',''varchar(max)'') 
		, 1, 2, '''') ) AS ServerRolePermissions
	,(SELECT 
		STUFF( 
			(SELECT '', <'' + dpr.permission_name + ''>'' 
				FROM sys.database_principals dp
					INNER JOIN sys.database_permissions dpr
						ON dp.principal_id = dpr.grantee_principal_id
				WHERE dp.sid = pr.sid
				ORDER BY dpr.permission_name 
				FOR XML PATH(''''), type 
				).value(''(./text())[1]'',''varchar(max)'') 
		, 1, 2, '''') ) AS DBRolePermissions
	FROM sys.server_principals pr
		LEFT OUTER JOIN sys.server_role_members srm
			ON pr.principal_id = srm.member_principal_id
		INNER JOIN sys.server_permissions sp
			ON sp.grantee_principal_id = pr.principal_id
		LEFT OUTER JOIN sys.server_principals roleprin
			ON srm.role_principal_id = roleprin.principal_id
		INNER JOIN #Principals tp
			ON pr.name = tp.UserorGroup'
;
 
INSERT INTO #PermsAudit
        ( DBName ,
          ServerRole ,
          PrincipalName ,
          PrincipalPerms ,
          ServerRolePermissions ,
          DBRolePermissions
        )
	EXECUTE (@SQL);
 
FETCH NEXT FROM getperms INTO @DBToQuery;
 
END
 
CLOSE getperms;
DEALLOCATE getperms;
 
SELECT *
	FROM #PermsAudit;
 
DROP TABLE #Principals;
DROP TABLE #DBs;
DROP TABLE #PermsAudit;
GO

As you look through this code, you will notice that I have done a few interesting things.  Here is a quick recap:

  • Build a temp table based on a delimited list of databases input into a variable
  • Build a temp table based on a delimited list of logins input into a variable
  • Used a string splitter known as DelimitedSplit8K by Jeff Moden (google it) or LMGTFY
  • Used FOR XML to build a delimited string of Server Role Permissions
  • Used FOR XML to build a delimited string of Database Role Permissions
  • Used a CURSOR to build a dynamic SQL string to execute for each database

Holy cow! That is a lot of stuff for such a simple quick audit.  A cursor? Seriously? Well yeah! It is OK in this type of scenario to use a cursor.  This is a well-performing cursor for an administrative function. In addition, I chose to use sub-queries to build my delimited string of permissions so I could keep those things together in a single record per login/role.

If I run that query on my instance of SQL Server, I would end up with something that looks like the following.

Now, obviously, unless you had these same databases, logins, and even the same permissions assigned to these logins, your results will be different. So, make sure you go and change those inputs for logins and databases to be queried.

Please share this

Leave a Reply

Related Articles

A toy machine and a microphone on a table Description automatically generated

The Right Tool for the Right Job

I’m looking forward to 2024. From many perspectives, I think we are going to see great advancements, including in technology, that on one hand will make our lives easier, but also make our decisions a bit harder.

Read More »