I was approached with an in shop issue where a group could not view the execution reports in the SSISDB. The reason for this was due to the security standards in place at the shop; the user could not be allowed to have the SSIS_Admin role.

In order to allow the user group access to the review Integration Services reports on package executions the SSIS_Admin role had to be given. This role would allow the following capabilities:

· Import Packages

· Delete own packages

· Delete all packages

· Change own package roles

· Change all package roles

This role also elevates privileges to sysadmin. This elevation of privilege can occur because these roles can modify Integration Services packages and Integration Services packages can be executed by SQL Server using the sysadmin security context of SQL Server Agent.

What this document will provide is a solution around not granting the SSIS_Admin role, but still allow the necessary entities access to the SSIS execution reports without granting any write, execute, create, update, or insert access.

The Catalog Views

Microsoft has two catalog views that make up the report access for SSIS packages (2012 and later) that I have found. We will look at both of these views and alter them to comment out the where clause. In doing so this will negate only SSIS_admin and sysadmins from having access to the reports, but will allow other users access to these reports.

Catalog.Event_Messages

The first catalog view we will look at is catalog.event_messages. This view is simply utilized for displaying information about messages that were logged during operations. The way Microsoft has configured this view is to only allow the SSIS_Admin or sysadmin privilege to view. In our case we would like to have other groups the ability to review the messages only. In order to do this we need to alter the catalog view.

To access the catalog view navigate to the SSISDB on the SQL Instance. Right click the catalog view and say alter:

Next comment out the where clause and execute the alter statement updating the catalog view appropriately

Catalog.executions

The second catalog view can be accessed in the same manner as the first catalog by repeating the same methodology of altering the view. Catalog.executions displays the instances of the package execution in the Integration Services catalog. Packages that are executed with the Execute Package task run the same instance of execution as the parent package.

After right clicking and altering the view, the where clause will need to be commented out as shown below:

After the where clause is commented out execute the alter command to update the catalog view.

Granting Catalog Access

After altering the catalog views we are left with a manual but needed process. Within the Integration Services Catalogs the following steps will need to be completed:

1. Right click on the package and go to properties

2. Once the Folder Properties dialog box is initiated go to Browse in the upper right hand corner

3. Once the Browse All Principals dialog box is initiated select the public database role and click ok

4. Ensure the grant read access is then given to the database role and click ok

Conclusion

By taking the above steps the users can now see the reports and that is it. In testing I’ve found that users cannot do the following:

· Create new environmental variables

· Create new packages

· Create new folders

· Cannot add, insert, update, or delete anything in the SSISDB or the Catalog Folders

· Cannot initiate any SSIS SQL Agent jobs

· Cannot execute any queries against the SSISDB

What I’ve found users can do the following:

· See package names

· Right click and select all executions

· View the reports

**As with anything do not take code from the web and blindly implement into your production environment.