Sql Server – Integration Services – SSIS Catalog ( SSISDB ) – Metadata – List Permissions

Introduction

Quick follow-up to our last post, here is how to retrieve permission set for our SSIS Catalog ( SSISDB).

BTW, here is a link to our last post.

 

SQL



use [SSISDB]
go

; with ctePermissionType
(
	  [permissionTypeID]
	, [permissionName]
)
as
(

	select 1, 'Read'

	union
	select 2, 'Modify'

	union
	select 3, 'Execute'

	union
	select 4, 'ManagePermissions'

	union

	select 100, 'CreateObjects'

	union

	select 101, 'ReadObjects'

	union
	select 102, 'ModifyObjects'

	union
	select 103, 'ExecuteObjects'

	union
	select 104, 'ManageObjectPermissions'

)
, ctePermission
as
(

	SELECT DISTINCT 
	      [catalog]
			= cat.[name]

		, [principal]
			= pris.[name]

		, [isDeny]
			= pers.[is_deny]

		, [permisionType]
			= pris.[type] 

		, ctePT.[permissionTypeID]

		, ctePT.permissionName

		, [granted]
			= case
				when pers.is_deny = 1 then 'No'
				else 'Yes'
			 end

	FROM [catalog].[folders] cat

	INNER JOIN [SSISDB].[catalog].[explicit_object_permissions] pers  

		on cat.folder_id = pers.[object_id]

	INNER JOIN [SSISDB].[internal].[get_database_principals]() pris  
		ON pers.principal_id = pris.principal_id  

	INNER JOIN ctePermissionType ctePT
		ON pers.permission_type = ctePT.permissionTypeID

)


select 
		  ctePerm.[catalog]
		, ctePerm.[principal]

		, [read]
			= isNull
				(
						ctePerm.[Read]
					, 'No'
				)

		, [modify]
			= isNull
				(
						ctePerm.[Modify]
					, 'No'
				)

		, [execute]
			= isNull
				(
					ctePerm.[Execute]
					, 'No'
				)

		, [managePermissions]
			= isNull
				(
					ctePerm.[ManagePermissions]
					, 'No'
				)

		, [createObjects]
			= isNull
				(
					ctePerm.[CreateObjects]
					, 'No'
				)

		, [readObjects]
			= isNull
				(
					ctePerm.[ReadObjects]
					, 'No'
				)

		, [modifyObjects]
			= isNull
				(
					ctePerm.[ModifyObjects]
					, 'No'
				)

		, [executeObjects]
			= isNull
				(
					  ctePerm.[ExecuteObjects]
					, 'No'
				)

		, [manageObjectPermissions]
			= isNull
				(
					  ctePerm.[ManageObjectPermissions]
					, 'No'
				)

from 
	(

		select
			  ctePerm.[catalog]
			, ctePerm.[principal]
			, ctePerm.[granted]
			, ctePerm.[permissionName]

		from   ctePermission ctePerm

	)
	ctePerm

pivot (

		 max 
		 (
			[granted]
		 )       

		for ctePerm.[permissionName] in 
		(
			  [Read]
			, [Modify]
			, [Execute]
			, [ManagePermissions]

			, [CreateObjects]
			, [ReadObjects]
			, [ModifyObjects]
			, [ExecuteObjects]
			, [ManageObjectPermissions]
		)
	
	)       

     as [ctePerm]

order by
		  ctePerm.[catalog]
		, ctePerm.[principal]








Output

 

One thought on “Sql Server – Integration Services – SSIS Catalog ( SSISDB ) – Metadata – List Permissions

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