Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

BIT Barbarian

After working in the mortgage industry for 7 years, I transitioned into Business Intelligence and began learning SQL and .NET. My goal is to integrate my business knowledge into my development to intelligently analyze and find solutions to problems. Blogging offers me an option to share what I've learned as well as receive feedback on better practices and solutions.

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

 

Comments

Leave a comment on the original post [www.bitbarbarian.com, opens in a new window]

Loading comments...