When you’re sharing an on-premises SSIS server with multiple teams you’d want to segregate responsibilities to their projects. It’s not a much advertised feature of SSISDB, but it allows you to implement quite fine grained access to the SSIS objects. I won’t be able to cover every possible scenario, but with a standard use case, you’ll know where to look when implementing more complex configurations.

Scenario

You’re storing your SSIS projects in SSISDB with project deployment model and have 2 projects:

  • Project HR, which can only be seen by the developers for HR
  • Project Sales, which can only be seen by the developers for Sales

We have 4 groups of users:

  • The administrator should be able to view and manage both projects
  • The IT manager should be able to see all objects, but not execute or change them
  • The HR development team should have full control over HR-ETL project and can not see the Sales-ETL project
  • The Sales development team should have full control over Sales-ETL and can not see the HR-ETL project

SSISDB Database Roles

A first level of security is the SQLServer roles, where following two are relevant:

  • ssis_admin : members have full administrative access to the SSISDB
  • ssis_logreader : this role provides permissions to access all the views related SSISDB operational logs

Our administrator team could be given the ssis_admin role and have full control.

Catalog permissions

These can be split in 2 levels : Folder and Project permissions.

Folder permissions

Right-click on a folder (e.g. HR under SSISDB in the example) and choose Properties. On the left you’ll find a tab Permissions with following settings:

  • Read : allows to see the folder and its properties, but does not allow to projects/packages/environments in the folder
  • Modify : allows to change the folder but nothing else
  • Manage Permissions : Allow to change the permissions, which is a bit dangerous, as it allows him to give all access.
  • Create Objects : creates objects in the folders
  • Modify Objects : change objects in the folders
  • Execute Objects : execute objects in the folders
  • Read Objects : allows to see all objects within the folder
  • Manage Object Permissions : change all permissions on all objects in the folder.

This way, we can give the IT manager read access by granting Read and Read Objects permissions on the HR folder and the Sales folder. If new projects are added within those folders, he will see them.

We can assign the HR teams the permission to create/modify/execute/read objects on the HR folder, which allows them to deploy, change and run packages.

Similary, we can assign the Sales teams the permission to create/modify/execute/read objects on the Sales folder, which allows them to deploy, change and run packages.

Project Permissions

You can even go deeper to the project level and manage permissions by right-clicking the project and selecting properties.

There are only a few of the above permissions, as it pertains to the project. The permission are applied to all packages in a project.

This way, you can set different access levels to separate projects in the same folder.

It is also possible to manage Environment permissions, which can help you to specify who can only read environment variables or also change them.

Conclusion

This article is mainly meant to show what is available in SSISDB security management. There are many possible scenario’s, but with these pointers, you should be able to quickly setup the right access.

Find out more configuration details here : https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-ver15#Permissions

Read More

If you’re interesting in SQLServer and SSIS, make sure to read other articles like Power BI for SQL Server Agent jobs or Expressions on ConnectionString issue in SSIS 2019