Permission to view execution report in SSIS Catalog

We are currently using SSIS 2012. Is there any way for a user to view execution reports under SSIS Catalog without being ssis_admin or sysadmin?

This is for production environment and we don't want people to manipulate SSIS Catalog projects.

Here is our solution (believe me it will work perfectly!)

After investigating the procedure about how the execution reports are stored, we found that every time a job runs, the internal.executions table in SSISDB will be updated. And in order to view the execution report of this run, we need to run something like below:

EXEC SSISDB.catalog.grant_permission 
    @object_type = 4, 
    @object_id = @execution_id, 
    @principal_ID =  13, 
    @permission_type = 1;

This stored procedure will grant a role/user a certain access to an object in database. @object_type means on which type of object you need permissions on (4 means operation); @object_id means the specific object we want to access to; @principal_ID means who want to get the access; permission_type means what kind of access we want to have (1 means read only). For more information, please refer to catalog.grant_permission (SSISDB Database)

Our target is to create a trigger that every time a job runs -- meaning the internal.executions table gets inserted -- using the above SP to grant a role the permission to that operation information.

Then, let's follow the below steps to setup execution report viewing permissions:

-
Create a user the trigger will be executed as. This user should be able to execute a trigger in SSISDB and have access to SSIS Catalog. In our case, we give it db_owner role and ssis_admin under SSISDB.

USE [master]
GO
CREATE LOGIN [ssis_job_viewer] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [SSISDB]
GO
CREATE USER [ssis_job_viewer] FOR LOGIN [ssis_job_viewer]
GO
USE [SSISDB]
GO
ALTER ROLE [db_owner] ADD MEMBER [ssis_job_viewer]
GO
USE [SSISDB]
GO
ALTER ROLE [ssis_admin] ADD MEMBER [ssis_job_viewer]
GO

-
Create a role [package_execution_viewer]. This role will be used in the stored procedure we mentioned above.

USE [SSISDB]
GO
CREATE ROLE [package_execution_viewer]
GO

-
Add users to [package_execution_viewer]

USE [SSISDB]
GO
ALTER ROLE [package_execution_viewer] ADD MEMBER [user1]
GO
USE [SSISDB]
GO
ALTER ROLE [package_execution_viewer] ADD MEMBER [user2]
GO

-
Get the principle_id of package_execution_viewer role. This id will be used in above SP also.

SELECT * from sys.database_principals
GO

-
Create trigger to grant permission for package_execution_viewer

USE [SSISDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [internal].[update_viewer_perms]
ON [internal].[executions]
WITH EXECUTE AS 'ssis_job_viewer'
AFTER INSERT
AS
declare @execution_id bigint
BEGIN
select @execution_id = execution_id from inserted
EXEC SSISDB.catalog.grant_permission 
    @object_type = 4, 
    @object_id = @execution_id, 
    @principal_ID =  13, 
    @permission_type = 1   **--Note the principal_id needs to be changed**
END
GO

All set. This way we can let people access to the execution reports without making them as ssis_admin. Try it out and share any thoughts to this post!

StackExchange Database Administrators Q#78341, answer score: 13