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;
GOAs 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.
