People often confused that having access to SSISDB will also provide access to the Integration Service Catalog folders but permission for the folder is handled separately.
The following code can be used to grant read only access to the Integration Service Catalog folder
EXEC [SSISDB].[catalog].[grant_permission] @object_type=1, @object_id=2, @principal_id=11, @permission_type=1 GO EXEC [SSISDB].[catalog].[grant_permission] @object_type=1, @object_id=2, @principal_id=11, @permission_type=101 GO
The “object_type” parameter is used to specify the type of securable.
1 for folder
2 for Project
3 for environment
The second parameter is “object_id”, it is the Id of the folder for which the access needs to be granted. The “object_id” of the folder can be retrieved from the catalog view
“select * from ssisdb.catalog.folders”
The third parameter is “principal_id” from SSISDB. The “principal_id” of the user can be retrieved from the system view
“select * from sys.database_principals”
The last parameter is “permission_type”, there are different level of permissions such as read, modify, execute, etc., for the complete list of permissions and their detailed description refer BOL. “Permission_type” 1 and 101 were assigned to the user account.
It is important to note that the permission must be granted to both READ (1), READ_OBJECTS (101). When the “READ” permission alone is granted user can only view the folder but can not view the projects, packages inside the folder as the “READ” permission don’t enumerate or read the contents of other objects within the folder. So, “READ_OBJECTS” permission also should be given along with “READ” for the accounts with windows authentication.
An account with SQL Server Authentication behaves slightly different that is the “READ” permission allows the user to see all the objects under the folder but it doesn’t show the properties of it. To view the properties of the objects inside the folder the “READ_OBJECTS” permission must be granted.