Monday, August 6, 2012

Folder Security in SSIS 2012

Deploying my first SSIS 2012 Project Deployment Mode project to the SSIS Catalog was easy. Mainly because I was a sysadmin on the server. When I tried to tighten down the security a little bit things got a little confusing. One of our developers were itching to deploy a new project to our development server so I went ahead and added him to the ssis_admins role in the catalog database but this isn't how I wanted things to be moving forward.


There are two parts to securing the SSIS Catalog. They are both simple, but one is a little obscure if you like to jump into things without reading the documentation.

Typically, we like to add users to a role when we grant them access to a database. This is not the case with the SSIS Catalog unless you need (note: I said need as in really need) to give someone full control to your catalog. For typical users, you'll just want to add them as a user in the SSISDB database without adding them to a role. They get basic access with the public role and that should be all they need. More granular permissions are configured in the next step.

Granting permissions on the SSIS Catalog objects is quite simple. You can do it through SSMS or via T-SQL though I don't see the point in going the T-SQL route unless you're scripting a task. Even then I think I would use PowerShell and SMO.

To set permissions just right-click on the object you want to set the permissions on (a folder, project, or environment) and choose Properties... From there, select the Permissions tab and you'll get a dialog box like the one shown below.
I chose the guest account for illustration purposes only

That should look familiar if you have set permissions on a database object before. The permissions, however are a bit different. The following table (copied from catalog.grant_permission (SSISDB Database)) shows the permissions you can set on each object and how you can expect them to behave.
permission_type Value Permission Name Permission Description Applicable Object Types
1 READ Allows the principal to read information that is considered part of the object, such as properties. It does not allow the principal to enumerate or read the contents of other objects contained within the object. Folder, Project, Environment, Operation
2 MODIFY Allows the principal to modify information that is considered part of the object, such as properties. It does not allow the principal to modify other objects contained within the object. Folder, Project, Environment, Operation
3 EXECUTE Allows the principal to execute all packages in the project. Project
4 MANAGE_PERMISSIONS Allows the principal to assign permissions to the objects. Folder, Project, Environment, Operation
100 CREATE_OBJECTS Allows the principal to create objects in the folder. Folder
101 READ_OBJECTS Allows the principal to read all objects in the folder. Folder
102 MODIFY_OBJECTS Allows the principal to modify all objects in the folder. Folder
103 EXECUTE_OBJECTS Allows the principal to execute all packages from all projects in the folder. Folder
104 MANAGE_OBJECT_PERMISSIONS Allows the principal to manage permissions on all objects in the folder. Folder

If you want to give someone access to view the SSIS Catalog reports but not do anything else in the database, I am afraid that you are out of luck. As described in this Microsoft Connect case, it didn't make it into RTM: SSIS Catalog doesn't have option to give read access to SSIS Catalog to view package run reports. If a new case pops up I will post a link to it.


You can read more about this topic in the SSISDB Catalog article on MSDN and in chapter 22 of the Wrox Press book Professional Microsoft SQL Server 2012 Integration Services. Ke Yang also has a detailed step-by-step guide on the SSIS Blog: SSIS Catalog Access Control Tips.

If you have more information to share please post a comment!

No comments:

Post a Comment