This post is part of a series about SSIS Catalog Security. The other posts in this series are:
- One Way to Set SSIS Catalog Security
- One Way to Script SSIS Catalog Permissions
- SSIS Catalog Browser v0.9.9.3
Suppose you are an enterprise DBA or Data Architect, and you would like to grant Software Developers read-only access to SSIS Catalog Folders in production. One way to solve this problem is to use a database role. I believe in communication-by-example, so let’s begin. I urge you to practice and test in a non-production environment first.
Remember to always back up the SSISDB before tinkering with settings of any kind.
Create a New Database Role
Open SQL Server Management Studio (SSMS) and connect Object Explorer to the SQL Server instance that hosts the SSIS Catalog. Expand Databases > SSISDB > Security > Roles, right-click “Database Roles,” and then click “New Database Role…”:
When the Database Role – New window displays:
- Enter a name for the role in the Role name property
- Click the Add button to begin adding Role Members:
Clicking the Add button opens the “Select Database User or Role” dialog. Click the Browse button to add Database Users and/or Roles:
When the “Browse for Objects” dialog displays, select the database user(s) and/or role(s) to whom you wish to grant read-only SSIS Catalog access:
Click the OK button to return to the “Select Database User or Role” dialog. Note the user(s) and/or role(s) selected in the previous step are now listed in the “Enter the object names to select” property:
Click the OK button to return to the Database Role – New window. Note the member(s) selected now appear in the “Members of this role” list:
The role has been created.
Learn how to use this new role in my post titled One Way to Set SSIS Catalog Security.
As always, I welcome your feedback, suggestions, thoughts, and concerns.