To view the SSIS permission list

The below script to provides the all SSIS user permissions


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