SSIS Catalog Access Control Tips

SSIS Catalog Access Control Tips

Rate This
  • Comments 7

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 TypesGroups 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!

Leave a Comment
  • Please add 4 and 4 and type the answer here:
  • Post
  • Great blog post, thanks.

  • Your blog is great, SSIS when did it start? Is this the best way to start making different catalogs?

  • What do you mean by "SSIS when did it start?"?

    We support an in-stock catalog, SSISDB. To have different catalogs you may want to (and mean to) have different instances.

  • I have followed step 2 to allow a login (user or group) to be able to read/execute all projects in a folder (although I have only allowed Read and ReadObjects permissions as I just want the user to be able to view).

    The problem is I cannot view anything in [catalog].[executions] or [catalog].[operations].

    I can see rows from packages, folders etc. but not executions or operations

    Any clues how I can view [catalog].[executions] etc?

  • 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.

    www.biadmin.com/.../folder-security-in-ssis-2012.html

    I am disapointed

  • I've granted the end-user public access to the database and I've created a subfolder under SSISDB catalog where I've granted the end-user full control, but they can't see the folder when they attempt to deploy there package

  • Case 2: "To allow a login (user or group) to be able to read/execute all projects in a folder:" does not actually work when you have previously set specific Project level security. The project will not automatically inherit the folder permission anymore. As a workaround, the project should be deleted and re-deployed.

Page 1 of 1 (7 items)