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

,

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

 

Rate

Share

Share

Rate