Sql-server – (SQL Server /SSIS) Managing IS Catalog permissions on a shared server

sql serverssis

I'm hoping for some ideas on how you and your team manage this. I'm supporting a shared SQL Server that will have several different departments deploying their SSIS packages via the IS Catalog. I would like users to be able to have some level of access to their packages, without that colliding with/seeing the packages from other groups. I envision users being able to:

  • Go to 'Integration Services Catalogs' -> SSISDB -> And have read-only access to the folders containing the projects and environments they have deployed. Possibly full access to deploy new ISPAC to that folder, but only for folders they are granted permissions to.
  • See the IS catalog execution reports for only their SSIS packages.
  • Query the SSISDB database for only information (executions, package names, parameters) related to their packages.

How do you do this in a shared environment, where more granular permissions are needed?

Best Answer

You probably want to add Users in the msdb database to the db_ssisltduser role as per the Microsoft Docs on Integration Services Roles. Under the Read and Write Actions section for the db_ssisltduser role it says the following permissions will be applied:

Enumerate own packages.

Enumerate all packages.

View own packages.

Execute own packages.

Export own packages. Import packages.

Delete own packages.

Change own package roles.

You can also make a custom role that encompasses some of these permissions and/or other permissions as needed too.

Related Question