SQL 2012 SSIS Catalog Permissions

With the introduction of the SQL 2012 SSIS Catalog there has been the question of what permissions are available and how can I secure projects deployed to the Catalog. Here is a brief run down of the available permissions as well as how to set them up.

First For a User to even see anything in the SSIS Catalog they will need permission to the SSISDB. If you want to give someone full access to both the data base and the SSIS catalog you can add them to the database role of ssis_admin. However to walk through how all the permissions work I am going to start by giving my test user just public access.

For each Folder in the SSIS Catalog you can secure as follows:

  1. Read
  2. Modify
  3. Manage Permissions
  4. Create Objects
  5. Modify Objects
  6. Execute Objects
  7. Read Objects
  8. Manage Object Permissions

The first three are related to the root folder of the Project Granting Read, Modify or Manage Permissions will only effect the root folder and in my opinion are honestly pointless. If you give a user permission to read just the folder all they can see is the project and environment folder. The modify from everything I can tell does nothing because the only option you can perform at the folder level is to rename the folder but if you try to rename the folder you will get an error saying you have to be a member of the ssis_admin role in the SSISDB and that role gives access to everything. As for manage permissions if you have that access you can give yourself access to everything else.

Read Access at the Folder Level

For Each Project the permissions can be defined as:

  1. Read
  2. Modify
  3. Execute
  4. Manage Permissions

To access permissions of the folder right click on the folder and select Properties then select permission and for a project navigate to the project and right click on the project and select Properties then select permisions.

Folder Level Access Project Level Access

To try and break down the remaining possibilities I created the chart below

Desired Access SSISDB permissions SSIS Catalog Folder permissions SSIS Individual Project Permissions
See the project exists Public Read None
Manage all permissions Public Read, Manage Permissions N/A
Execute All SSIS Packages in any Project Public Read, Execute Objects N/A
Modify All SSIS Packages in any Project Public Read, Modify Objects N/A
See all Packages in any Project Public Read, Read Objects N/A
Manage permissions to all packages in any Project Public Read, Manage Object Permissions N/A
Execute SSIS Packages in a specific Project Public Read Execute
Modify SSIS Packages in a specific Project Public Read Modify
See all SSIS Packages in a specific Project Public Read Read
Manage permissions to SSIS Packages in a specific Project Public Read Manage Permissions
Change Folder Names and Manage All objects ssis_admin N/A N/A

3 thoughts on “SQL 2012 SSIS Catalog Permissions

  1. Anne June 14, 2016 / 5:31 pm

    I found an issue using SSISDB catalog, if I execute the package in the db context, and if data source is a UNC path file, it fails with error message Access denied and cannot open the file.
    Searched online, it says the server has to be configured as kerboros authentication. This is really a pain. I have no problem to run the same package using package deploy mode

    • James Phillips March 27, 2017 / 7:13 pm

      That is correct you do have to have kerberos configuration setup for UNC path with the catalog. It’s due to how the process is launched which is different than package calling the dtexec command line.

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