http://www.sqlservercentral.com/blogs/bit-barbarian/2013/05/31/tsql-snippet-for-viewing-basic-info-on-database-principals-and-their-permissions/

Printed 2014/10/31 10:13PM

TSQL Snippet for viewing basic info on database principals and their permissions

By g00p3, 2013/05/31

Quick snippet I put together for reviewing basic info on database users/principals, permissions, and members if the principal is a role.

/*******************************************************
Some Basic Info on Database principals, permissions, 
explicit permissions, and if role, who is in this role currently
*******************************************************/
;
with
	roleMembers
	as (
			select
				drm.role_principal_id
				,dp.principal_id
				,dp.name
			from
				sys.database_role_members drm

				inner join sys.database_principals dp
				on drm.member_principal_id = dp.principal_id
		)

select
	db_name()
	,dp.name
	,general_permissions = stuff
	( (
		select distinct
			', ' + permission_name
		from
			sys.database_permissions p
		where
			dp.principal_id = p.grantee_principal_id
			and p.major_id = 0
			and p.state = 'G'
		for xml path (''), type
	)
	.value( '.', 'varchar(max)' ), 1, 1, '' )
	,deny_permissions =
	stuff
	( (
		select distinct
			', ' + permission_name
		from
			sys.database_permissions p
		where
			dp.principal_id = p.grantee_principal_id
			and p.major_id = 0
			and p.state = 'D'
		for xml path (''), type
	)
	.value( '.', 'varchar(max)' ), 1, 1, '' )

	,specific_permissions =
	stuff
	( (
		select distinct
			', ' + permission_name + ' on ' + object_schema_name( p.major_id ) + '.' + object_name( p.major_id )
		from
			sys.database_permissions p
		where
			dp.principal_id = p.grantee_principal_id
			and major_id <> 0
		for xml path (''), type
	)
	.value( '.', 'varchar(max)' ), 1, 1, '' )
	,current_active_members =
	stuff
	( (
		select distinct
			', ' + r.name
		from
			roleMembers r
		where
			r.role_principal_id = dp.principal_id
		for xml path (''), type
	)
	.value( '.', 'varchar(max)' ), 1, 1, '' )
from
	sys.database_principals dp
order by
	dp.name asc

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.