Helpful information and examples on how to use SQL Server Integration Services.
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:
If you already get it, you can stop the reading here :-) Otherwise, let us take a real world scenario for example. Consider below requirements:
Below, we’ll take steps to apply Case 1 to DevA and DevB, apply Case 2 to SSISOps, and apply Case 3 to SSISAdmins.
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.
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.
Thank you, this really helped me a lot.
I wish I'd found this post about a month ago. By far the best (clearest, briefest, most complete - all at once, no less) SSIS Catalog security article I've found. Thank you - you will save me so much time.