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!