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:
- 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:
- Map it to a member of the SSISDB database role — public. (This leverages SQL security mechanism.)
- Grant it Read to the folder, and grant it Read/Execute to the project. (This uses SSIS Catalog security mechanism.)
- To allow a login (user or group) to be able to read/execute all projects in a folder:
- Map it to a member of the SSISDB database role — public.
- Grant it Read/Execute/Read Objects to the folder.
- To allow a login to be able to do anything on SSISDB:
- 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:
- 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.
- One group of SSIS operators (Group SSISOps) that can run packages in projects deployed by developers in both group DevA and DevB.
- 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.
- 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.
- Under User Mapping, click the checkbox for SSISDB.
- Map the login to be a member of public role.
- Right-click on each folder where the projects are in.
- Choose Permissions.
- 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.)
- Add SSISOps, grant Read and Execute and Read Objects permissions. See below for SSISOps’ example.
- Right-click on each project.
- Choose Permissions.
- Click Browse, select DevA for ProjectA, select DevB for ProjectB.
- Grant Read and Execute to the login.
- Create a SQL Server Login for Group SSISAdmin.