Database Permissions Queries

3

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%'

3 thoughts on “Database Permissions Queries

  1. […] ————————————-VVMIMP——————————–https://sqlstudies.com/2013/08/05/database-permissions-queries/————————————-VVMIMP——————————–&#8211; Database & object PermissionsSELECT 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_StatementFROM sys.database_permissions PermissionJOIN sys.database_principals Grantee ON Permission.grantee_principal_id = Grantee.principal_idJOIN sys.database_principals Grantor ON Permission.grantor_principal_id = Grantor.principal_idLEFT OUTER JOIN sys.all_objects [Objects] ON Permission.major_id = [Objects].object_id order by 1–WHERE Grantee.name LIKE ‘%MyUserName%’ […]

  2. SQLpro says:

    The query “Database & object Permissions” is false.
    Example :
    ———————-
    CREATE DATABASE DB_SECURITY;
    GO
    USE DB_SECURITY;
    GO
    CREATE TABLE T1 (C1 INT, C2 INT);
    GO
    CREATE LOGIN CNX_01 WITH PASSWORD = ”, DEFAULT_DATABASE = DB_SECURITY;
    CREATE USER USR_01 FROM LOGIN CNX_01;

    GRANT SELECT ON T1 TO USR_01;

    CREATE TABLE T2 (C21 INT, C22 INT);
    GO

    GRANT SELECT ON T2 (C22) TO USR_01;

    CREATE SCHEMA SSS

    CREATE TABLE SSS.T (C INT)

    GRANT SELECT, INSERT, UPDATE ON SCHEMA::SSS TO USR_01

    CREATE TYPE TYP
    FROM INT;

    GRANT REFERENCES ON TYPE::TYP TO USR_01
    ———————-
    This must conduct to such à GRANTs order :

    EXECUTE AS USER = ‘dbo’; GRANT CONNECT ON DATABASE::DB_SECURITY TO dbo; REVERT;
    EXECUTE AS USER = ‘dbo’; GRANT CONNECT ON DATABASE::DB_SECURITY TO USR_01; REVERT;
    EXECUTE AS USER = ‘dbo’; GRANT SELECT ON [dbo].[T1] TO USR_01; REVERT;
    EXECUTE AS USER = ‘dbo’; GRANT SELECT ON [dbo].[T2] (C22) TO USR_01; REVERT;
    EXECUTE AS USER = ‘dbo’; GRANT INSERT ON SCHEMA::SSS TO USR_01; REVERT;
    EXECUTE AS USER = ‘dbo’; GRANT SELECT ON SCHEMA::SSS TO USR_01; REVERT;
    EXECUTE AS USER = ‘dbo’; GRANT UPDATE ON SCHEMA::SSS TO USR_01; REVERT;
    EXECUTE AS USER = ‘dbo’; GRANT REFERENCES ON TYPE::TYP TO USR_01; REVERT;

    Yout query give this answer :

    USR_01 dbo DATABASE CONNECT NULL GRANT REVOKE CONNECT FROM [USR_01]; GRANT CONNECT TO [USR_01] AS [dbo];
    USR_01 dbo OBJECT_OR_COLUMN SELECT T1 GRANT REVOKE SELECT ON [T1] FROM [USR_01]; GRANT SELECT ON [T1] TO [USR_01] AS [dbo];
    USR_01 dbo OBJECT_OR_COLUMN SELECT T2 GRANT REVOKE SELECT ON [T2] FROM [USR_01]; GRANT SELECT ON [T2] TO [USR_01] AS [dbo];
    USR_01 dbo SCHEMA INSERT sysrowsets GRANT REVOKE INSERT ON [sysrowsets] FROM [USR_01]; GRANT INSERT ON [sysrowsets] TO [USR_01] AS [dbo];
    USR_01 dbo SCHEMA SELECT sysrowsets GRANT REVOKE SELECT ON [sysrowsets] FROM [USR_01]; GRANT SELECT ON [sysrowsets] TO [USR_01] AS [dbo];
    USR_01 dbo SCHEMA UPDATE sysrowsets GRANT REVOKE UPDATE ON [sysrowsets] FROM [USR_01]; GRANT UPDATE ON [sysrowsets] TO [USR_01] AS [dbo];
    USR_01 dbo TYPE REFERENCES NULL GRANT NULL NULL

    1) incomplete for TYPE
    2) incomplete with COLs GRANT

    I did a query that do the job :

    http://blog.developpez.com/sqlpro/p12897/ms-sql-server/une-requete-pour-lister-les-privileges-au-niveau-des-utilisateurs-sql

    A +

  3. I’ll agree these queries are incomplete. The latest versions are listed under https://sqlstudies.com/free-scripts/sp_srvpermissions/ and https://sqlstudies.com/free-scripts/sp_dbpermissions/ These versions should give you a more complete look at the data.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,653 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: