SSISCatalogReaderRole

I needed to give a group of business intelligence specialists read permsisions on the SSIS Catalog. I created this scritp to achieve this goal. This script greats a database role with the required permissions. At last I mapped a Windows Group to this database role.

USE SSISDB
GO
create role SsisCatalogReadRole authorization dbo

-- declare variables
declare @object_type_folder smallint = 1
declare @object_type_project smallint = 2
declare @object_id bigint
declare @principal_id int = (select principal_id from sys.database_principals as dp where dp.name =  'SsisCatalogReadRole')
declare @permission_type_read smallint = 1
declare @permission_type_read_objects smallint = 101

-- grand read and read_object permisions for each folder
DECLARE @folder_id bigint

DECLARE grant_folder_permission_cursor CURSOR FOR  
select folder_id from catalog.folders

OPEN grant_folder_permission_cursor   
FETCH NEXT FROM grant_folder_permission_cursor INTO @folder_id

WHILE @@FETCH_STATUS = 0   
BEGIN   
  
  EXEC [SSISDB].[catalog].[grant_permission] @object_type=@object_type_folder, @object_id=@folder_id, @principal_id=@principal_id, @permission_type=@permission_type_read
  EXEC [SSISDB].[catalog].[grant_permission] @object_type=@object_type_folder, @object_id=@folder_id, @principal_id=@principal_id, @permission_type=@permission_type_read_objects
  
  FETCH NEXT FROM grant_folder_permission_cursor INTO @folder_id
END
CLOSE grant_folder_permission_cursor   
DEALLOCATE grant_folder_permission_cursor 

-- grand read permision on projects in each folder
DECLARE @project_id bigint

DECLARE grant_project_permission_cursor CURSOR FOR  
select project_id from catalog.projects

OPEN grant_project_permission_cursor   
FETCH NEXT FROM grant_project_permission_cursor INTO @project_id

WHILE @@FETCH_STATUS = 0
BEGIN 

  EXEC [SSISDB].[catalog].[grant_permission] @object_type=@object_type_project, @object_id=@project_id, @principal_id=@principal_id, @permission_type=@permission_type_read

  FETCH NEXT FROM grant_project_permission_cursor INTO
@project_id
END
CLOSE grant_project_permission_cursor
DEALLOCATE grant_project_permission_cursor