Home
%3CLINGO-SUB%20id%3D%22lingo-sub-388057%22%20slang%3D%22en-US%22%3ESSIS%20Catalog%20Access%20Control%20Tips%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-388057%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Mar%2019%2C%202012%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3ESSIS%202012%20introduces%20significant%20improvements%20in%20how%20SSIS%20packages%20are%20deployed%2C%20configured%20and%20managed%20in%20a%20centralized%20SSIS%20catalog.%20The%20SSIS%20catalog%20corresponds%20to%20a%20user%20database%2C%20called%20SSISDB.%20You%20can%20secure%20the%20SSIS%20objects%20in%20the%20SSIS%20catalog%20(folders%2C%20projects%2C%20environments%2C%20operations%2Fexecutions)%20using%20a%20combination%20of%20SQL%20security%20and%20security%20capabilities%20provided%20by%20SSIS.%20Months%20back%2C%20a%20friend%20asked%20on%20how%20to%20set%20SSIS%20catalog%20access%20control%20in%20a%20real%20world%20scenario.%20I%E2%80%99d%20like%20to%20share%20our%20thoughts%20here%2C%20see%20if%20it%20helps!%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBriefly%2C%20consider%20below%20cases%3A%3C%2FP%3E%3CBR%20%2F%3E%3COL%3E%3CBR%20%2F%3E%3CLI%3ETo%20allow%20a%20login%20to%20be%20able%20to%20read%2Fexecute%20only%20one%20project%2C%20but%20not%20able%20to%20access%20other%20objects%20(projects%20or%20environments)%20in%20a%20folder%20where%20the%20project%20is%20in%3A%3COL%3E%3CBR%20%2F%3E%3CLI%3EMap%20it%20to%20a%20member%20of%20the%20SSISDB%20database%20role%20--%20%3CB%3Epublic%20%3C%2FB%3E.%20(This%20leverages%20SQL%20security%20mechanism.)%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EGrant%20it%20%3CB%3ERead%20%3C%2FB%3Eto%20the%20folder%2C%20and%20grant%20it%20%3CB%3ERead%2FExecute%20%3C%2FB%3Eto%20the%20project.%20(This%20uses%20SSIS%20Catalog%20security%20mechanism.)%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FOL%3E%0A%20%20%20%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ETo%20allow%20a%20login%20(user%20or%20group)%20to%20be%20able%20to%20read%2Fexecute%20all%20projects%20in%20a%20folder%3A%3COL%3E%3CBR%20%2F%3E%3CLI%3EMap%20it%20to%20a%20member%20of%20the%20SSISDB%20database%20role%20--%20%3CB%3Epublic%20%3C%2FB%3E.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EGrant%20it%20%3CB%3ERead%2FExecute%2FRead%20Objects%20%3C%2FB%3Eto%20the%20folder.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FOL%3E%0A%20%20%20%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ETo%20allow%20a%20login%20to%20be%20able%20to%20do%20anything%20on%20SSISDB%3A%3COL%3E%3CBR%20%2F%3E%3CLI%3Emake%20it%20a%20member%20of%20the%20SSISDB%20database%20role%20--%20%3CB%3Essis_admin%20%3C%2FB%3E.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FOL%3E%0A%20%20%20%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FOL%3E%3CBR%20%2F%3E%3CP%3EIf%20you%20already%20get%20it%2C%20you%20can%20stop%20the%20reading%20here%20%3A)Otherwise%2C%20let%20us%20take%20a%20real%20world%20scenario%20for%20example.%20Consider%20below%20requirements%3A%3C%2FP%3E%3CBR%20%2F%3E%3COL%3E%3CBR%20%2F%3E%3CLI%3ETwo%20groups%20of%20developers%20(Group%20%3CI%3EDevA%20%3C%2FI%3Eand%20%3CI%3EDevB%20%3C%2FI%3E)%20and%20they%20must%20not%20be%20able%20to%20see%20each%20other%20deployed%20projects%3B%20%3CI%3EDevA%20%3C%2FI%3Ecan%20only%20run%20packages%20in%20%3CI%3EProjectA%2C%20DevB%20%3C%2FI%3Ecan%20only%20run%20packages%20in%20%3CI%3EProjectB%20%3C%2FI%3E.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EOne%20group%20of%20SSIS%20operators%20(Group%20%3CI%3ESSISOps%20%3C%2FI%3E)%20that%20can%20run%20packages%20in%20projects%20deployed%20by%20developers%20in%20both%20group%20%3CI%3EDevA%20%3C%2FI%3Eand%20%3CI%3EDevB%20%3C%2FI%3E.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EOne%20group%20of%20SSIS%20administrators%20(Group%20%3CI%3ESSISAdmins%20%3C%2FI%3E)%20that%20can%20do%20anything.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FOL%3E%3CBR%20%2F%3E%3CP%3EBelow%2C%20we%E2%80%99ll%20take%20steps%20to%20apply%20Case%201%20to%20%3CI%3EDevA%20%3C%2FI%3Eand%20%3CI%3EDevB%20%3C%2FI%3E%2C%20apply%20Case%202%20to%20%3CEM%3ESSISOps%20%3C%2FEM%3E%2C%20and%20apply%20Case%203%20to%20%3CEM%3ESSISAdmins%20%3C%2FEM%3E.%3C%2FP%3E%3CBR%20%2F%3E%3COL%3E%3CBR%20%2F%3E%3CLI%3ECreate%20SQL%20Server%20Logins%20for%20the%20groups%20%3CI%3EDevA%20%3C%2FI%3E%2C%20%3CI%3EDevB%2C%20%3C%2FI%3E%3CI%3ESSISOps%20%3C%2FI%3E(%20%3CB%3ENote%20%3C%2FB%3E%3A%20make%20sure%20when%20you%20add%20the%20group%2C%20under%20%3CB%3EObject%20Types%20%3C%2FB%3E%E2%80%93%20%3CB%3EGroups%20%3C%2FB%3Eis%20checked).%20See%20below%20for%20SSISOps%E2%80%99%20example.%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F99761iB55D37B28CA22621%22%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3COL%3E%3CBR%20%2F%3E%3CLI%3EUnder%20%3CB%3EUser%20Mapping%20%3C%2FB%3E%2C%20click%20the%20checkbox%20for%20SSISDB.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EMap%20the%20login%20to%20be%20a%20member%20of%20%3CB%3Epublic%20%3C%2FB%3Erole.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FOL%3E%3CBR%20%2F%3E%3CLI%3ERight-click%20on%20each%20folder%20where%20the%20projects%20are%20in.%3C%2FLI%3E%3CBR%20%2F%3E%3COL%3E%3CBR%20%2F%3E%3CLI%3EChoose%20%3CB%3EPermissions.%20%3C%2FB%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EAdd%20%3CI%3EDevA%20%3C%2FI%3E%2C%20%3CI%3EDevB%20%3C%2FI%3E%2C%20grant%20%3CB%3ERead%20%3C%2FB%3Epermission.%20(%20%3CB%3ENote%20%3C%2FB%3E%3A%20if%20you%20grant%20%3CB%3ERead%20Objects%20%3C%2FB%3Epermissions%2C%20then%20%3CI%3EDevA%20%3C%2FI%3Eand%20%3CI%3EDevB%20%3C%2FI%3Ewould%20be%20able%20to%20read%20all%20projects%2Fenvironments%20under%20this%20folder%2C%20which%20is%20not%20desired%20in%20this%20scenario.)%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EAdd%20%3CI%3ESSISOps%20%3C%2FI%3E%2C%20grant%20%3CB%3ERead%20%3C%2FB%3Eand%20%3CB%3EExecute%20%3C%2FB%3Eand%20%3CB%3ERead%20Objects%20%3C%2FB%3Epermissions.%20See%20below%20for%20%3CI%3ESSISOps%E2%80%99%20%3C%2FI%3Eexample.%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F99762iBE29E0F2021992C5%22%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FOL%3E%3CBR%20%2F%3E%3CLI%3ERight-click%20on%20each%20project.%3C%2FLI%3E%3CBR%20%2F%3E%3COL%3E%3CBR%20%2F%3E%3CLI%3EChoose%20%3CB%3EPermissions.%20%3C%2FB%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EClick%20%3CB%3EBrowse%20%3C%2FB%3E%2C%20select%20%3CI%3EDevA%20%3C%2FI%3Efor%20%3CI%3EProjectA%20%3C%2FI%3E%2C%20select%20%3CI%3EDevB%20%3C%2FI%3Efor%20%3CI%3EProjectB.%20%3C%2FI%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EGrant%20%3CB%3ERead%20%3C%2FB%3Eand%20%3CB%3EExecute%20%3C%2FB%3Eto%20the%20login.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FOL%3E%3CBR%20%2F%3E%3CLI%3ECreate%20a%20SQL%20Server%20Login%20for%20Group%20%3CI%3ESSISAdmin.%20%3C%2FI%3E%3C%2FLI%3E%3CBR%20%2F%3E%3COL%3E%3CBR%20%2F%3E%3CLI%3EUnder%20%3CB%3EUser%20Mapping%20%3C%2FB%3E%2C%20click%20the%20checkbox%20for%20SSISDB.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EMake%20sure%20this%20login%20is%26nbsp%3B%20a%20member%20of%20the%20SSISDB%20database%20role%20%E2%80%93%20%3CB%3Essis_admin%20%3C%2FB%3E%2C%20see%20below%3A%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F99763i2B222ABD073CEE72%22%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FOL%3E%0A%20%20%3C%2FOL%3E%3CBR%20%2F%3E%3CP%3EThat%E2%80%99s%20it!%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-388057%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Mar%2019%2C%202012%20SSIS%202012%20introduces%20significant%20improvements%20in%20how%20SSIS%20packages%20are%20deployed%2C%20configured%20and%20managed%20in%20a%20centralized%20SSIS%20catalog.%3C%2FLINGO-TEASER%3E
Not applicable
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!