August 5, 2013 by Kenneth Fisher
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%'