Blog Post

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 AccessProject Level Access

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

Desired AccessSSISDB permissionsSSIS Catalog Folder permissionsSSIS Individual Project Permissions
See the project existsPublicReadNone
Manage all permissionsPublicRead, Manage PermissionsN/A
Execute All SSIS Packages in any ProjectPublicRead, Execute ObjectsN/A
Modify All SSIS Packages in any ProjectPublicRead, Modify ObjectsN/A
See all Packages in any ProjectPublicRead, Read ObjectsN/A
Manage permissions to all packages in any ProjectPublicRead, Manage Object PermissionsN/A
Execute SSIS Packages in a specific ProjectPublicReadExecute
Modify SSIS Packages in a specific ProjectPublicReadModify
See all SSIS Packages in a specific ProjectPublicReadRead
Manage permissions to SSIS Packages in a specific ProjectPublicReadManage Permissions
Change Folder Names and Manage All objectsssis_adminN/AN/A

Rate

Share

Share

Rate