SSIS Catalog Access Control Tips

SSIS 2012 introduces significant improvements in how SSIS packages are deployed, configured and managed in a centralized SSIS catalog. The SSIS catalog corresponds to a user database, called SSISDB. You can secure the SSIS objects in the SSIS catalog (folders, projects, environments, operations/executions) using a combination of SQL security and security capabilities provided by SSIS. Months back, a friend asked on how to set SSIS catalog access control in a real world scenario. I’d like to share our thoughts here, see if it helps!

Briefly, consider below cases:

1. To allow a login to be able to read/execute only one project, but not able to access other objects (projects or environments) in a folder where the project is in:
   1. Map it to a member of the SSISDB database role -- public. (This leverages SQL security mechanism.)
   2. Grant it Read to the folder, and grant it Read/Execute to the project. (This uses SSIS Catalog security mechanism.)
2. To allow a login (user or group) to be able to read/execute all projects in a folder:
   1. Map it to a member of the SSISDB database role -- public.
   2. Grant it Read/Execute/Read Objects to the folder.
3. To allow a login to be able to do anything on SSISDB:
   1. make it a member of the SSISDB database role -- ssis_admin.

If you already get it, you can stop the reading here :-) Otherwise, let us take a real world scenario for example. Consider below requirements:

1. Two groups of developers (Group DevA and DevB) and they must not be able to see each other deployed projects; DevA can only run packages in ProjectA, DevB can only run packages in ProjectB.
2. One group of SSIS operators (Group SSISOps) that can run packages in projects deployed by developers in both group DevA and DevB.
3. One group of SSIS administrators (Group SSISAdmins) that can do anything.

Below, we’ll take steps to apply Case 1 to DevA and DevB, apply Case 2 to SSISOps, and apply Case 3 to SSISAdmins.

1. Create SQL Server Logins for the groups DevA, DevB, SSISOps (Note: make sure when you add the group, under Object Types – Groups is checked). See below for SSISOps’ example.
1. Under User Mapping, click the checkbox for SSISDB.
2. Map the login to be a member of public role.
2. Right-click on each folder where the projects are in.
1. Choose Permissions.
2. Add DevA, DevB, grant Read permission. (Note: if you grant Read Objects permissions, then DevA and DevB would be able to read all projects/environments under this folder, which is not desired in this scenario.)
3. Add SSISOps, grant Read and Execute and Read Objects permissions. See below for SSISOps’ example.
   
3. Right-click on each project.
1. Choose Permissions.
2. Click Browse, select DevA for ProjectA, select DevB for ProjectB.
3. Grant Read and Execute to the login.
4. Create a SQL Server Login for Group SSISAdmin.
1. Under User Mapping, click the checkbox for SSISDB.
2. Make sure this login is  a member of the SSISDB database role – ssis_admin, see below:
   

That’s it!