Reports In SSISDB

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:

clip_image002

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

clip_image004

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:

clip_image006

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

image

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

image

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

image

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.

Advertisements

3 Comments

  1. 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
    stored procedure
    4. Create new SQL Agent job
    5. Create new step in this job to execute the below command in SSISDB database
    exec [dbo].[GivePermissionsToSSISReport]
    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.

    Many thanks.
    Regards

    Orietta

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s