0

I am trying to give a user (AD Group) the ability to list all Integration Services Catalog projects and packages, execute all packages, and read the package logs. I DO NOT want them to be able to upload or modify Integration Services Catalog projects or packages.

This is on SQL Server 2016 using SSIS Project Deployment.

Based on this document (https://learn.microsoft.com/en-us/sql/integration-services/security/integration-services-roles-ssis-service?view=sql-server-2016) I have granted the group with the following roles:

  • msdb.db_datareader
  • msdb.db_ssisoperator
  • SSISDB.db_datareader
  • SSISDB.ssis_logreader

When the user expands Integration Services Catalog, they can see the "SSISDB" catalog, but nothing within it.

I have tried giving the user the SSISDB.ssis_admin role, and this allows them to enumerate the projects and packages, but it also seems to give them INSERT/UPDATE/DELETE/MODIFY permissions which is not acceptable.

Note that this SSISDB catalog does include projects when viewed by users in the msdb.db_ssisadmin and SSISDB.ssis_admin database roles.

Here is what an SSIS Admin sees compare with the new Operator:

meadek
  • 1
  • 3

1 Answers1

0

After rereading the documentation several times, I finally caught this note in the first paragraph of the page:

The available roles are different depending on whether you're saving packages in the SSIS Catalog database (SSISDB) or in the msdb database.

Since our packages are all in SSISDB, the msdb roles don't matter in our case. This explained why no matter what I did with the msdb roles, nothing changed in Integration Services.

I also realized that the [SSISDB].[ssis_logreader] database role only allows access to the SSISDB Reports by default, but doesn't allow navigation of the folder/project/package structure.

In order to allow navigation of the structure, permission must be manually granted at the folder and project level by a ssis_admin or sysadmin user. However, I found that at the Project level (at least in our environment) the "public" role has full permissions, which seems odd.

screenshot: public permissions at the project level

meadek
  • 1
  • 3