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.
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
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
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.
I found out the same problem and same solution an year ago, but Microsoft Support declared me that if we modify this code we risk the lost of the support because this behaviour is “by design” (I opened an SR regarding the SSIS all execution report).
The work around suggested by support is the following:
1. In SSISDB database create a new ROLE ssis_reports
2. Assign any user you want to read reports to this role
3. In SSISDB database create the GivePermissionsToSSISReport
4. Create new SQL Agent job
5. Create new step in this job to execute the below command in SSISDB database
6. Save the job and schedule it with the desired frequency. This job will assign permissions required to view the reports.
I think that your solution is better because is easier to implement and to manage and I’ve request a Design Change to Microsoft that, unfortunatly, will not be implemented in the current available versions of the product.
Did you have to write your own stored procedure for GivePermissionsToSSISReport or did Microsoft Support give it to you? If you have the code, could you share it?
No, these procedures were pre-existing in the SSISDB, and they are utilized when running the standard reports.