The SSIS Catalog has built-in security to manage permissions. SSISDB – the database behind the SSIS Catalog – is “just a database” in many respects. When it comes to security, the SSIS Catalog surfaces an internal mechanism that provides granular permissions management. 
In this post I intend to demonstrate how to use SSIS Catalog security to provide read-only access to SSIS Catalog artifacts. But first…

Two Thoughts About SSISDB Roles

Thought 1: “Help us DBAs, you’re our only hope.” – Princess Leia (paraphrased)

If you work with SSIS you already know the Microsoft team of technical writers is comprised of artists, masters of the field of technical writing. I’m convinced a large part of the successful adoption of SSIS is due these good people. You can see a sample of their outstanding artistry in the article titled Integration Services Roles (SSIS Service).

Two important roles in the SSIS Catalog are ssis_admin and ssis_logreader. According to the article linked above:

ssis_admin. This role provides full administrative access to the SSIS Catalog database.

ssis_logreader This role provides permissions to access all the views related SSISDB operational logs.

SSIS_admin and ssis_logreader are SQL Server database roles. As such, they are typically set and maintained by Database Administrators (DBAs).

Thought 2: Although SSISDB is a SQL Server database, it’s more like an application coded in T-SQL.

One for-instance, for instance, is the SSIS Catalog requires Windows authentication for most administrative activities. It took me a while to understand why Windows authentication is necessary. I now get it, but the explanation is for another post. This has implications, such as:

• SQL Logins – even sysadmins such as sa – cannot deploy SSIS projects to an SSIS Catalog. Or execute SSIS packages.
  • One exception: an Azure-SSIS SSISDB database hosted on an instance of Azure SQL DB can perform SSIS Catalog administration using a SQL Login.

In sum, the SSIS Catalog is a database application that requires Windows authentication for administrative tasks.

Null Use Case: No Access

When a user has no access to SSIS Catalog artifacts, the SSMS Object Explorer Integration Services Catalogs node appears as shown here:

SSIS Catalog Browser presents a unified view of all SSIS Catalog artifacts (and it’s free!).

All SSIS Catalog-related products and utilities at DILM Suite respect SSIS Catalog security. Early versions of SSIS Catalog Compare allowed users to login with SQL Server Login credentials and access SSIS Catalog artifacts that SSIS Catalog security would block. A couple years ago I refactored CatalogBase – the object beneath DILM Suite’s SSIS Catalog products and utilities that interacts with the SSIS Catalog – to respect SSIS Catalog security.

A user with no access will see an empty SSIS Catalog using SSIS Catalog Browser:

Grant Read-Only Access to Folders

In SSMS, right-click the SSIS Catalog Folder you wish to surface for a 
Windows authentication-based SQL Server login:

When the Folder Properties window displays, click the Permissions page. On the Permissions page, click the Browse button to select one or more Windows authentication logins:

The Browse All Principals dialog displays. Select one or more Windows authentication logins (Windows User type):

To assign read-only permission to the SSIS Catalog Folder, click the OK button on the Browse All Principals dialog and check the Grant checkbox for the Read permission in the Folder Properties window:

The user(s) you selected are now able to view the SSIS Catalog folder using the SSMS Object Explorer’s Integration Services Catalogs node:

Similarly, SSIS Catalog Browser will display the SSIS Catalog folder for the assigned user(s);

“Where’s the Beef?”

I can hear you thinking, “Where are the projects and environments, Andy?” That is an excellent question. I’m glad you asked! They exist, of course, but we only granted the user Read permission for the SSIS Catalog Folder.

Grant Read-Only Access to Folder Artifacts

To see SSIS Projects and SSIS Catalog Environments, a user must be granted Read Objects permission on the SSIS Catalog Folder:

Now users can view SSIS Catalog folder contents using SSMS:

SSIS Catalog Browser users enjoy a similar view:

Conclusion

The SSIS Catalog surfaces a rich and powerful security model that may be leveraged by enterprise data integration DevOps teams using SSMS or SSIS Catalog Browser.

Learn More About the SSIS Catalog, ADF, and SSIS from Me!

Check out training at Enterprise Data & Analytics!

Need Help Getting Started or Updating SSIS in Your Enterprise?

Enterprise Data & Analytics specializes in training and helping enterprises modernize their data engineering by lifting and shifting SSIS from on-premises to the cloud. Our experienced engineers grok enterprises of all sizes. We’ve done the hard work for large and challenging data engineering enterprises. We’ve earned our blood-, sweat-, and tear-stained t-shirts. Reach out. We can help.