I’ll show how permissions in SSIS SQL Server 2012/2014 are set, managed and queried.

With SQL Server 2012 Microsoft introduced a new Project Deployment Model with a dedicated SQL Server Database. DTSX Packages are no longer deployed into the MSDB but in the SSISDB. It’s a database, so you can manage security as in any other database but you also have the chance to set the security for every folder, project or environment.

Every Permission you set on an item like folder, project or environment is stored in the SSISDB in the corresponding table. In the screenshot you can see the example for folders and the corresponding “folder_permissions” table.

For this example I created two users (“Environment_Changer” and “SSIS_Executor001”). We will give them permissions corresponding to their names. I created two different folder for two different Integration Services projects and in each folder there are two different environments.

For the first project we will set the permissions on folder level on the second project we will explicitly set the level on the environment to show the different levels of permissions.

The next screenshots show the effective permissions set via Management Studio:

As you can see the user “Environment_Changer” has granted the permissions to read and change the objects in the project folder.

The SSIS_Executor001 is allowed to read and execute the objects in the folder but there is no grant to modify.

In the second folder I gave permissions on the Environment-Level only. User “Environment_Changer” is allowed to read and modify objects.

And here the SSIS_Executor001 has an explicit deny on the modify permission.

This we can see and manage in the GUI. For every object there is a corresponding table for permissions.

For our first example with the folder permission we can use the following two queries to get a list of folders and permissions set on the folder level:

We do have two folders and we can query the permissions. As all kind of information is presented by numbers I wrote a query to have it more readable. In this query I get the folder and the environment permissions. I intentionally used the “union all” to give the chance to use only parts of the query and have to comprehend a recursive query.

table #Permisiontypes

This gives the following result:

 We can find all the permissions including the explicit deny we already saw in the management studio GUI.

This result can now be used in different ways. One idea could be two store it somewhere and after some month to compare it with the current state. So we have lightweight solution to audit the object permissions in the SSISDB.