This post is part of a series about SSIS Catalog Security. The other posts in this series are:

• One Way to Create an SSIS Catalog Read-Only Database Role
• One Way to Script SSIS Catalog Permissions
• SSIS Catalog Browser v0.9.9.3

SSIS Catalog security allows Enterprise DBAs and Data Architects to fine tune access and permissions for SSIS Catalogs. Managing security is part of Data Integration Lifecycle Management, or DILM.

I can hear some of you thinking, …

“How May I Allow Developers Read-Only Access, Andy?”

That’s a fantastic question! I’m so glad you asked.

Open SQL Server Management Studio (SSMS) and then open Object Explorer. Connect to the SQL Server instance that hosts the SSIS Catalog for which you want to allow developers read-only access. Expand the Integration Services Catalogs node, expand the SSISDB node, right-click the folder for which you want to allow developers read-only access, and then click Properties:

When the Folder Properties window displays, click the Browse button to search for SQL Server principals:

I recommend first creating a database role and adding developers to that role.

When the Browse All Principals dialog displays, select the login(s), user(s), or role(s) for which you want to grant access:

 

Click the OK button to return to the Folder Properties window when you are finished selecting principals.

To grant developers read-only access to the SSIS Catalog Folder, select the principal(s) and then check the Grant checkboxes for Read and Read Objects permissions:

Click the OK button to complete the operation.

So configured, a developer connecting to SSMS Object Explorer and expanding Integration Services Catalogs > SSISDB may now view the SSIS Catalog Folder(s) without permission to modify folder artifacts. This configuration may be desirable for production SSIS Catalogs.

A Peek Under the Hood

The SSIS Catalog includes a stored procedure named grant_permission in the [catalog] schema. Grant_permission takes four parameters:

1. object_type
2. object_id
3. principal_id
4. permission_type

According to the documentation, there are four object_types: folder (1), project (2), environment (3), and operation (4). From a read-only perspective, the SSIS Catalog Folder – object_type 1 – is the highest in the SSIS Catalog hierarchy (Catalog > Folder > Project or Environment > Package or Environment Variable). If you want developers to be able to read all SSIS Catalog artifacts contained within a Catalog Folder, granting read-only permissions at the Catalog Folder scope is one way to go.

I encourage you to learn more from the documentation and by visiting the other posts in this series (listed at the top of this post).

As always, I welcome your feedback, suggestions, thoughts, and concerns.

:{>