Permission to view execution report in SSIS Catalog

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:

1. 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
   

2. 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
   

3. 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
   

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

   SELECT * from sys.database_principals
   GO
   

5. 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!

With the caveat that I'm not a security person...

There is no predefined database role other than ssis_admin that is special to the SSISDB. That allows one to do all the SSIS things but that's clearly more power than a support person should have.

There are two schemas, internal and catalog. Catalog is meant for us, end users to interact with the SSISDB while internal is, to quote a great manual

IST NICHT FÜR DER GEFINGERPOKEN UND MITTENGRABEN!

I fired up profiler and watched as I clicked through the execution reports and the subreports. All the queries are in-line queries against the catalog schema. The procs and function that are in catalog schema all appear to be related to the maintenance and administration of the packages so if you crated a role that

You can run with Martin's answer to grant access to all the catalog based views but as I'm lazy,

• Is there a way to grant a user access to only all non system views of database?

I'd try something like this. I Create a role called LookIt, add my member(s) to it and then give them SELECT permission to the entire catalog schema

USE [SSISDB]
GO
CREATE ROLE [LookIt]
GO
USE [SSISDB]
GO
ALTER ROLE [LookIt] ADD MEMBER [MyPeople]
GO
use [SSISDB]
GO
GRANT SELECT ON SCHEMA::[catalog] TO [LookIt]
GO

Edit 2015-10-08

Rejoice, for those of you looking at SQL Server 2016. There is a new SSIS role coming that will allow non-privileged users the ability to use the native reporting tools. That role is called ssis_logreader Granting membership to that role will allow users to access all the reporting without granting them the ability to administer the SSIS instance or the entire server.

Very simply... Comment out the WHERE clause in these two views:

SSISDB.catalog.executions
SSISDB.catalog.event_messages

Done.

Change the view catalog.event_messages by commenting out the WHERE clause:

--WHERE opmsg.[operation_id] in (SELECT [id] FROM [internal]. 
--[current_user_readable_operations]) OR (IS_MEMBER('ssis_admin') = 1) OR 
--(IS_SRVROLEMEMBER('sysadmin') = 1)

Do the same to the view Catalog.executions.

I have not encountered any side effects yet and have had it in place for 3 months in both PROD and QA environments.