Managing SSIS Security with Database Roles

By:   |   Comments (10)   |   Related: 1 | 2 | > Integration Services Security


SQL Server 2012 introduced the new project deployment model for Integration Services. On the server side, this manifests in the SSIS catalog, the central point for managing SSIS projects, environments and parameters. But how can we assign permissions for various tasks to different users inside the catalog? How do we protect execution info of sensitive packages? This tip will guide you through the process of setting up security for the SSIS 2012 catalog.


An introduction to the project deployment model is out of scope for this tip. For those interested in more information, I gladly refer you to this excellent tip: SSIS Package Deployment Model in SQL Server 2012 (Part 1 of 2).

The solution presented in this tip is valid from SQL Server 2012 till the latest version, when using the project deployment model.

SSIS Database Role

If people want to access the catalog, they need to be added to the SSISDB database. The first step in setting up security is assigning the correct groups/users to database roles. There is only one database role however: ssis_admin. Users belonging to this role can do pretty much anything in the catalog, except dropping it. There is not much documentation about this role, but you can check the securables of this role in the properties window:

SSISDB database roles

Obviously, sysadmins have also full privileges in the catalog. If you want to give users less permissions, you add them to the SSISDB database, but you don't assign them to any role (implicitly they are added to the public role). As with all database security, it is advised to work with groups and not with individual users. For demonstration purposes though, I will work with one single user in the examples. In the following screenshot, I added a database user with the name Koen to the SSISDB:

Adding a database user

This user can now log into the SSISDB database, but he cannot see the internal tables. He can see all of the catalog views, but they won't return any data (more on that later).

Where have all the tables gone

SSIS Permissions

If we want to give users appropriate permissions (without just adding them to the ssis_admin role), we'll need to assign them to the correct securables. The catalog has three securable objects: projects, environments and packages. Let's illustrate with an example:

In the catalog, two folders are created: FolderA and FolderB. Each folder contains a different project.

Two projects sitting in a tree

Using Management Studio, we can easily assign permissions to the user created earlier. Let's give the user Koen read permissions to folder A. Don't forget to assign the permission Read Object, otherwise the project inside the folder will not be accessible.

Assigning permissions

When this user now opens the catalog, he can only see FolderA, but FolderB is hidden. The user can see the project and the packages, but he cannot modify or execute them.

FolderB has disappeared

An important permission is Manage Object Permission. With this permission you can delegate the administration of permissions to other users without adding them to the ssis_admin role. For more information about the different permissions, see the table in the Remarks section of this MSDN page.

Important note: the catalog security doesn't work well with SQL Server users. Try to use domain accounts/groups as much as possible. Using SQL Server authentication results in the following error message: An exception occurred while executing stored procedure 'some internal stored procedure'. The error message is: 'The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.'

A great feature of the catalog security is that the available catalog views in the SSISDB are filtered according to the permissions given to the user. For example, if the user Koen checks the view catalog.executions, he can only see information about FolderA, for which he has permissions.

A little more rowss

However, if a sysadmins or a user belonging to the role ssis_admin queries the same view, he retrieves all of the data.

Not so much rows

As you can see, there were multiple executions in FolderA, but the user can only see his own executions. This can become problematic if someone who is not an admin wants to follow-up the executions of the SSIS packages, as described in this Connect item. A work around could be to develop your own reporting framework on top of the SSIS catalog.

Remark: there was an issue with Windows Groups and the catalog security, but a fix has been released with SQL Server 2012 SP1 CU7.

T-SQL galore

One of the nicest features of the SSIS catalog is that you can do virtually everything that is possible through Management Studio with T-SQL scripts. This is also true for managing security of course. The most important stored procedure is probably catalog.grant_permission, which allows you to grant permissions to a securable object to users.


Security for the SSIS catalog is very intuitive and elegant to set-up. You can assign permissions on different objects to users and groups, but it works best with Windows Authentication. The catalog views are automatically filtered according to the permissions granted, but since users can only see their own executions reporting is not that straight forward. This might for example give issues in a production environment, where all the packages are executed with a single dedicated domain account.

Next Steps
  • For an introduction to the SSIS project deployment model, please refer to the following tip: SSIS Package Deployment Model in SQL Server 2012 (Part 1 of 2).
  • Try it out yourself! Create some dummy users in your environment, give them different permissions the catalog and see what effect it has on the various catalog views.
  • The MSDN article SSIS Catalog gives a good overview. It has a section on security and it contains a lot of links to different stored procedures you can use to automate your environment.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, March 30, 2015 - 8:45:40 PM - Vladimir Back To Top (36769)

Great answer. Anyway,  I requested Microsoft support on this issue and hope they can help me make it working.

Monday, March 30, 2015 - 4:31:30 AM - Koen Verbeeck Back To Top (36751)

@Vladimir: I've seen a lot of people with your issue, and most people use Agent jobs as a work around.

The reason you get this error is apparently because SSISDB uses REVERT in some of its procedures.

Wednesday, March 25, 2015 - 2:23:55 PM - Vladimir Back To Top (36689)

Thank you, I have a question though. How can I call SSIS from stored procedure using SSISDB stored proc([SSISDB].[catalog].[create_execution] etc)..? I need to pass parameters and that is why I need to use stored proc. If I call it (I have sysadmin rights on server with SSIS and place where package is processing data) I have "the current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again."

I have ugly work around by having stored proc creating job, run another job to change ownership on this job to SQLAgent account(which has access on target server where SSIS process data), run this job and drop this job when process is done(checking system table when package completes).. I want to use normal elegant solution without this headache please..So what am I missing here.. ?

Thursday, May 15, 2014 - 10:15:48 AM - Rob Back To Top (30793)

Thanks for describing possible solutions, went with ssis_admin & db_denydatawriter

Tuesday, January 28, 2014 - 3:42:32 PM - Rachel Van den Berg Back To Top (29257)

I have also come across the problem with developers needing to be able to run the built-in SSIS reports, but not allowed to give them full sa or SSIS_admin on the production systems. After some testing I have come up with the solution to give them the SSIS_admin with datawrite_deny permissions. This stops them from making any changes or when they execute a package the writing deny stops the execution from proceeding, but gives them the possibilities to see the reports.

I hope in the next version the possibility to run the built-in SSIS reports will be solved more elegant


Monday, January 27, 2014 - 3:17:34 PM - DH Back To Top (29238)

Thank you Dave M! I never would think to look at security baked into the view definition itself. My guess is that this part of SSIS 2012 was low on the priority scale. The more important part was probably exposing SSIS to SSMS, the actual database, and the Integration Services Catalog.

Monday, January 27, 2014 - 12:03:54 PM - Koen Verbeeck Back To Top (29237)

There are indeed some shortcoming in the reporting layer of the SSISDB. I hope they make some necessary changes in the next release.

Monday, January 27, 2014 - 10:05:12 AM - Dave M Back To Top (29235)

It may not be the greatest solution but we added a role in the SSISDB called [SSISReportRead].  We then modified the all the catalog views to include this line at the end: 

OR (IS_MEMBER('SSISReportRead') = 1)


This will allow the members of this group to be able to access the data used by the reports.  I understand that when patching this could break, but we are willing to deal with that in order to not give too much access to production.





Monday, January 27, 2014 - 9:29:16 AM - DH Back To Top (29234)

There still is the outstanding issue of successfully running the built-in SSIS reports if you are NOT a member of the ssis_admin group OR an sa. When the end-user does not belong to one or both of these groups but does belong to the db_datareader db role in SSISDB, the reports will run but return no data. These reports are extremely helpful in resolving errors with SSIS execution (much better than the lackluster troubleshooting tools from previous versions). I have found no way around this and the SP1 CU7 update does not resolve this.

In my case, we will have a production SQL Server and SSIS 2012 instance. The BI developers would find the the reports very useful to resolve errors in production. However, corporate security rules will not allow them to be a member of the ssis_admin database role OR sa fixed server role in production and read access to the database does not appear to work.

Ive run profiler while the reports execute. The reports execute several dynamic SQL statements and varying parameters depending on the report and how deeply you dive into error chain.

Any ideas?

Thursday, January 16, 2014 - 10:38:21 AM - Thomas LeBlanc Back To Top (28101)


Excellent article. Thanks for showing the security and a good example.



get free sql tips
agree to terms