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())'',''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())'',''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.