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:
- Manage Permissions
- Create Objects
- Modify Objects
- Execute Objects
- Read Objects
- 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:
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|
Nice Article dude
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
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.