Over the last year of blogging my thoughts and queries I’ve discovered something interesting. I posted Where is my Backup? and found myself referring back to it over and over again. And that’s just one example. Turns out that for queries I tend to use frequently posting them here is a lot more continent than trying to keep a directory of .sql files. And for concepts that I’ve researched, but it’s been awhile and I could use a quick review, my own posts are the best reminders of what I already know that I can find.

To that end I’m posting a set of queries that I’ve written to help me to find out who has what permissions on a given database or server. I’ve found these handy for comparing the permissions for two users, copying the permissions from one user (or AD group) to another (please be very careful when doing this, you can grant someone far more permissions than you intend), confirming that someone has the permissions expected, or just reviewing someones permissions in general. Below are the database set with some descriptions. I’ll post the server set soon.

The last two columns of each query are “drop & create” scripts. (This includes revoke & grant for permissions and sp_droprolemember & sp_addrolemember for roles.) These scripts are a work in progress, and to be honest I’m not that great when it comes to Certificates and Keys, so I haven’t even started those yet. I will continue to update them over time, and if anyone finds a bug PLEASE let me know so that I can fix it. For that matter if anyone want’s to add to these columns feel free to post it and I’ll add it to the scripts.

Commented out at the bottom of each script is a WHERE clause. I put it there as a place holder for when I need to look up a specific individuals permissions instead of everyone’s. Obviously this isn’t the only criteria I ever need, it’s just the most common.

First here is a list of users/database principals (read about users/database principals vs logins/server principals here).

-- Database Principals
SELECT DBPrincipals.name, DBPrincipals.sid, DBPrincipals.type, DBPrincipals.type_desc, 
DBPrincipals.default_schema_name, DBPrincipals.create_date, DBPrincipals.modify_date,
DBPrincipals.is_fixed_role, Authorizations.name AS Role_Authorization,
CASE WHEN DBPrincipals.is_fixed_role = 0 THEN
'DROP ' + CASE DBPrincipals.[type] WHEN 'C' THEN NULL
WHEN 'K' THEN NULL
WHEN 'R' THEN 'ROLE'
WHEN 'A' THEN 'APPLICATION ROLE'
ELSE 'USER' END +
' '+QUOTENAME(DBPrincipals.name) + ';' ELSE NULL END AS Drop_Script,
CASE WHEN DBPrincipals.is_fixed_role = 0 THEN
'CREATE ' + CASE DBPrincipals.[type] WHEN 'C' THEN NULL
WHEN 'K' THEN NULL
WHEN 'R' THEN 'ROLE'
WHEN 'A' THEN 'APPLICATION ROLE'
ELSE 'USER' END +
' '+QUOTENAME(DBPrincipals.name) END +
CASE WHEN DBPrincipals.[type] = 'R' THEN
ISNULL(' AUTHORIZATION '+QUOTENAME(Authorizations.name),'')
WHEN DBPrincipals.[type] = 'A' THEN 
''
WHEN DBPrincipals.[type] NOT IN ('C','K') THEN 
ISNULL(' FOR LOGIN ' + QUOTENAME(SrvPrincipals.name),' WITHOUT LOGIN') + 
ISNULL(' WITH DEFAULT_SCHEMA =  '+QUOTENAME(DBPrincipals.default_schema_name),'')
ELSE ''
END + ';'
AS Create_Script
FROM sys.database_principals DBPrincipals
LEFT OUTER JOIN sys.database_principals Authorizations
ON DBPrincipals.owning_principal_id = Authorizations.principal_id
LEFT OUTER JOIN sys.server_principals SrvPrincipals
ON DBPrincipals.sid = SrvPrincipals.sid
AND DBPrincipals.sid NOT IN (0x00, 0x01)
--WHERE DBPrincipals.name LIKE '%MyUserName%'

Here are the database level roles. This one is pretty simple so I’m fairly confident about the drop/create scripts. This script has two commented out WHERE clauses, since I’m just as likely to pull for a given role (db_datareader for example) as a given user.

-- Database Role Members
SELECT Users.name AS UserName, Roles.name AS RoleName,
'EXEC sp_droprolemember '+QUOTENAME(Roles.name,'''')+','+QUOTENAME(Users.name,'''')+';' AS Drop_Script,
'EXEC sp_addrolemember '+QUOTENAME(Roles.name,'''')+','+QUOTENAME(Users.name,'''')+';' AS Add_Script
FROM sys.database_role_members RoleMembers
JOIN sys.database_principals Users
ON RoleMembers.member_principal_id = Users.principal_id
JOIN sys.database_principals Roles
ON RoleMembers.role_principal_id = Roles.principal_id
--WHERE Users.name LIKE '%MyUserName%'
--WHERE Roles.name LIKE '%DBRoleName%'

And last but not least, the database and object level permissions. This includes permissions such as standard GDR (although not revoke obviously) permissions for tables/stored procedures/functions etc and database level permissions such as CONNECT and VIEW DATABASE STATE.

-- Database & object Permissions
SELECT Grantee.name AS Grantee_Name, Grantor.name AS Grantor_Name,
Permission.class_desc, Permission.permission_name,
[Objects].name AS ObjectName, Permission.state_desc, 
'REVOKE ' +
CASE WHEN Permission.[state]  = 'W' THEN 'GRANT OPTION FOR ' ELSE '' END +
' ' + Permission.permission_name COLLATE SQL_Latin1_General_CP437_CI_AS + 
CASE WHEN Permission.major_id <> 0 THEN ' ON ' + QUOTENAME([Objects].name) + ' ' ELSE '' END +
' FROM ' + QUOTENAME(Grantee.name)  + '; ' AS Revoke_Statement,
CASE WHEN Permission.[state]  = 'W' THEN 'GRANT' ELSE Permission.state_desc COLLATE SQL_Latin1_General_CP437_CI_AS  END +
' ' + Permission.permission_name  + 
CASE WHEN Permission.major_id <> 0 THEN ' ON ' + QUOTENAME([Objects].name) + ' ' ELSE '' END +
' TO ' + QUOTENAME(Grantee.name)  + ' ' + 
CASE WHEN Permission.[state]  = 'W' THEN ' WITH GRANT OPTION ' ELSE '' END + 
' AS '+ QUOTENAME(Grantor.name)+';' AS Grant_Statement
FROM sys.database_permissions Permission
JOIN sys.database_principals Grantee
ON Permission.grantee_principal_id = Grantee.principal_id
JOIN sys.database_principals Grantor
ON Permission.grantor_principal_id = Grantor.principal_id
LEFT OUTER JOIN sys.all_objects [Objects]
ON Permission.major_id = [Objects].object_id
--WHERE Grantee.name LIKE '%MyUserName%'

Filed under: Dynamic SQL, Microsoft SQL Server, Security, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: code language, language sql, microsoft sql server, principals, security, sql statements, T-SQL, users