When using SSIS Catalog as a deployment target for Integration Services packages you can have quite versatile model for setting permissions but how would you export all the privileges from the environment? I tried to find sample SQL query but could not find any. That's why I spent some hours to figure out how the privileges are being saved so that we can have an easy query to run and export all the privileges.

Permission types used

SQL Server uses column permission_type to describe the actual user right. Within permission tables you can find permission_type column and it takes numerical values. Here is a list of possible values and their explanation:

Permission type	Explanation
1	Read
2	Modify
3	Execute (this permission is given to folder if any of the projects have execute permission)
4	Manage permissions
100	Create Objects
102	Modify Objects
103	Execute Objects
101	Read Objects
104	Manage Object Permissions

Tables used for saving permissions

SQL Server uses tables

internal.project_permissions
internal.folder_permissions
internal.environment_permissions

Tablenames are quite self-explanary which is good.

Database roles

In addition to SSIS catalog permissions SSIS also uses database roles for general level permissions. These roles are:

ssis_admin
ssis_logreader

You can look for explanation for these roles from: https://docs.microsoft.com/en-us/sql/integration-services/security/integration-services-roles-ssis-service?view=sql-server-2017

Query SSIS privileges

And finally the query that gets you all the privileges.

/* database roles */

select

'Database role' as [object_type],

rdp.name as [path],

mdp.name as [user] ,

null as permission_type,

null as [Permission]

from

sys.database_role_members drm

inner join

sys.database_principals rdp

on drm.role_principal_id = rdp.principal_id

inner join

sys.database_principals mdp

on drm.member_principal_id = mdp.principal_id

union all

/* folders */

SELECT

'folder' as [object_type],

f.name as [path],

pri.name as [user],

permission_type,

CASE

WHEN [obj].permission_type=1 THEN 'Read'

WHEN [obj].permission_type=2 THEN 'Modify'

WHEN [obj].permission_type=3 THEN 'Execution'

WHEN [obj].permission_type=4 THEN 'Manage permissions'

WHEN [obj].permission_type=100 THEN 'Create Objects'

WHEN [obj].permission_type=102 THEN 'Modify Objects'

WHEN [obj].permission_type=103 THEN 'Execute Objects'

WHEN [obj].permission_type=101 THEN 'Read Objects'

WHEN [obj].permission_type=104 THEN 'Manage Object Permissions'

END AS [Permission]

FROM

[internal].[object_permissions] AS obj

INNER JOIN

[sys].[database_principals] AS pri

ON obj.[sid] = pri.[sid]

INNER JOIN

[internal].[folders] as f

ON f.folder_id=obj.object_id

UNION ALL

/* projects */

SELECT

'project' as [object_type],

f.name + '/' + p.name as [path],

pri.name as [user],

permission_type,

CASE

WHEN [obj].permission_type=1 THEN 'Read'

WHEN [obj].permission_type=2 THEN 'Modify'

WHEN [obj].permission_type=3 THEN 'Execution'

WHEN [obj].permission_type=4 THEN 'Manage permissions'

WHEN [obj].permission_type=100 THEN 'Create Objects'

WHEN [obj].permission_type=102 THEN 'Modify Objects'

WHEN [obj].permission_type=103 THEN 'Execute Objects'

WHEN [obj].permission_type=101 THEN 'Read Objects'

WHEN [obj].permission_type=104 THEN 'Manage Object Permissions'

END AS [Permission]

FROM

[internal].[project_permissions] AS obj

INNER JOIN

[sys].[database_principals] AS pri

ON obj.[sid] = pri.[sid]

INNER JOIN

[internal].[projects] as p

ON p.project_id=obj.object_id

INNER JOIN

[internal].[folders] as f

ON p.folder_id=f.folder_id

UNION ALL

/* environments */

SELECT

'environment' as [object_type],

f.name + '/' + e.environment_name as [path],

pri.name as [user],

permission_type,

CASE

WHEN [obj].permission_type=1 THEN 'Read'

WHEN [obj].permission_type=2 THEN 'Modify'

WHEN [obj].permission_type=3 THEN 'Execution'

WHEN [obj].permission_type=4 THEN 'Manage permissions'

WHEN [obj].permission_type=100 THEN 'Create Objects'

WHEN [obj].permission_type=102 THEN 'Modify Objects'

WHEN [obj].permission_type=103 THEN 'Execute Objects'

WHEN [obj].permission_type=101 THEN 'Read Objects'

WHEN [obj].permission_type=104 THEN 'Manage Object Permissions'

END AS [Permission]

FROM

[internal].[project_permissions] AS obj

INNER JOIN

[sys].[database_principals] AS pri

ON obj.[sid] = pri.[sid]

INNER JOIN

[internal].[environments] as e

ON e.environment_id=obj.object_id

INNER JOIN

[internal].[folders] as f

ON e.folder_id=f.folder_id

Result set looks like this:

I hope this helps you!

Blog

‭(Hidden)‬ Blog Tools