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

What If…

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.

You are regularly backing up the SSISDB database, aren’t you? Don’t make me call Grant Fritchey (LinkedIn). The rumors are true: He is scary!

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:

1. Enter a name for the role in the Role name property
2. 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.

Conclusion

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.

:{>