SSIS Catalog Access Control Tips

[SSIS-Team]

First published on MSDN on Mar 19, 2012

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!