SQL Server – List Permissions for User

Background

Need a script for identifying permissions for a specific user.

Acknowledging

  1. Script to check database user permissions
    http://www.sqldbadiaries.com/2011/02/08/script-to-check-database-user-permissions/

Code

Stored Procedure




use master
go

if object_id('dbo.sp_ListObjectLevelPermissions') is null
begin

	exec('create procedure dbo.sp_ListObjectLevelPermissions as select 1/0 ')

end
go

alter procedure dbo.sp_ListObjectLevelPermissions
(
	@username sysname
)
as

begin

	/*
		Get Object Level Permissions
	*/
	SELECT
			  [UserName]
			= USER_NAME(dppriper.grantee_principal_id)
 
		, [principalType]
			= dppri.type_desc
 
		, [classDesc]
			= dppriper.class_desc
 
		, objectName
			=
			case dppriper.class_desc    
				when 'DATABASE' then db_Name(dppriper.major_id) 
				when 'OBJECT_OR_COLUMN'
					   then Object_Schema_Name(dppriper.major_id) 
									  + '.'
									  + object_Name(dppriper.major_id) 
				when 'SCHEMA' then schema_Name(dppriper.major_id) 
				when 'SYMMETRIC_KEYS' then
				   ( 
						select [name] 
						from sys.symmetric_keys 
						where dppriper.major_id
							   = symmetric_key_id
					)
					when 'CERTIFICATE' then
					(
						select [name] 
						from sys.certificates 
						where dppriper.major_id = certificate_id
					)
			end
 
		, [permissionName]
			= dppriper.permission_name
 
		, [permissionState]
			= dppriper.state_desc

		, [grantStatement]
			=
			case dppriper.class_desc    

				when 'DATABASE' 
					then
							  dppriper.state_desc + ' '
							+ dppriper.permission_name
							+ ' to '
							+ quotename(USER_NAME(dppriper.grantee_principal_id))
							+ ';'
								collate database_default


				when 'OBJECT_OR_COLUMN'
					   then dppriper.state_desc + ' '
								+ dppriper.permission_name
								+ ' on '
								+ quotename(Object_Schema_Name(dppriper.major_id)) 
								+ '.'
								+ quotename(object_Name(dppriper.major_id) )
								+ ' to '
								+ quotename(USER_NAME(dppriper.grantee_principal_id))
								+ ';'
								 collate database_default

				when 'SCHEMA' 
						then
								  dppriper.state_desc + ' '
								+ dppriper.permission_name
								+ ' on '
								+ ' SCHEMA :: '
								+ quotename(Schema_Name(dppriper.major_id)) 
								+ ' to '
								+ quotename(USER_NAME(dppriper.grantee_principal_id))
								+ ';'
								 collate database_default

				when 'SYMMETRIC_KEYS' 
					   then dppriper.state_desc + ' '
								+ dppriper.permission_name
								+ ' on '
								+ ' symmetric key::'
								+
								   ( 
										select quotename( [name] )
										from sys.symmetric_keys 
										where dppriper.major_id
											   = symmetric_key_id
									)
								+ ' to '
								+ quotename(USER_NAME(dppriper.grantee_principal_id))
								+ ';'
								 collate database_default

				when 'CERTIFICATE' 
					   then dppriper.state_desc + ' '
								+ dppriper.permission_name
								+ ' on '
								+ ' certificate::'
								+
								   ( 
										select quotename( [name] )
										from sys.[certificates] tblSC
										where dppriper.major_id
											   = certificate_id
									)
								+ ' to '
								+ quotename(USER_NAME(dppriper.grantee_principal_id))
								+ ';'
								 collate database_default



			end

			--, dppriper.major_id			
 
	FROM    sys.database_permissions dppriper
 
	INNER JOIN sys.database_principals dppri
 
		   ON dppriper.grantee_principal_id = dppri.principal_id
 
	where dppriper.grantee_principal_id 
		= case
				 when @username is null then dppriper.grantee_principal_id
			 
				 else user_id(@username)
 
			  end

	UNION ALL

	/*
		Get Role Permissions
	*/
	SELECT
			  [UserName]
			= USER_NAME(dppriper.member_principal_id)
 
		, [principalType]
			--= dppri.type_desc
 			= 'Database Role'

		, [classDesc]
			--= dppriper.class_desc
			= 'Database Role'
 
		, objectName
			= 'Database' collate database_default

		, [permissionName]
			= cast(dppriR.name as sysname) collate database_default

			 
		, [permissionState]
			= 'Grant' collate database_default

		, [grantStatement]
			= 'exec sp_addrolemember '
					+ '  @rolename = '
					+ quotename(dppriR.name)
					+ ' '
					+ ' ,'
					+ '  @membername = '
					+ quotename(USER_NAME(dppriper.member_principal_id))
					+ ' '
					+ ';'
				 collate database_default

 
    FROM	sys.database_role_members dppriper
 
	INNER JOIN sys.database_principals dppriM
 
		   ON dppriper.member_principal_id = dppriM.principal_id

	INNER JOIN sys.database_principals dppriR
 
		   ON dppriper.role_principal_id = dppriR.principal_id

	where dppriper.member_principal_id 
		= case
				 when @username is null then dppriper.member_principal_id
			 
				 else user_id(@username)
 
		  end


	order by
		  USERNAME
		, [principalType]
		, objectName
		, permissionName


end

go

grant execute on [dbo].[sp_ListObjectLevelPermissions] to [public]
go

EXEC sys.sp_MS_marksystemobject 'dbo.sp_ListObjectLevelPermissions'
go


Sample



   use [hrdb]
   go

   declare @username sysname

   set @username = 'appuser' 

   exec dbo.sp_ListObjectLevelPermissions
		@username = @username


References

Q/A

  1. How to check who has access to symmetric keys in SQL Server
    http://stackoverflow.com/questions/24211743/how-to-check-who-has-access-to-symmetric-keys-in-sql-server/33156146#33156146

Leave a Reply

Please log in using one of these methods to post your comment:

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