Stefan Grigat

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.

folder permission

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.

catalog folder structure

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:

permissions 01 folder 01

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


permissions 02 folder 01

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

permissions 01 folder 02

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

 permissions 02 folder 02

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:

 

SELECT TOP 1000
[folder_id],
[name],
[description],
[
created_by_sid]
FROM
[SSISDB].[internal].[folders]

 

SELECT TOP 1000
[id],
[sid],
[object_id],
[permission_type],
[is_deny],
[grantor_sid]
FROM

[SSISDB].[internal].[folder_permissions]

 

query results 01

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.

USE [SSISDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create table #Permisiontypes(
id
smallint,
description nvarchar(50)
)

 

insert into #Permisiontypes values
(
1,‚READ‘),
(2,‚MODIFY‘),
(3,‚EXECUTE‘),
(4,‚MANAGE_PERMISSIONS‘),
(100,‚CREATE_OBJECTS‘),
(101,‚READ_OBJECTS‘),
(102,‚MODIFY_OBJECTS‘),
(103,‚EXECUTE_OBJECTS‘),
(104,‚MANAGE_OBJECT_PERMISSIONS‘)

/*
Source of Info:
http://msdn.microsoft.com/en-us/library/ff878149.aspx */

 

SELECT     [Object] =  ‚Folder‘,
Foldername = fold.name,
Environmentname = NULL,
[permission_description] = #Permisiontypes.description,
Principals.Name,
[is_deny]
FROM
       [internal].[object_permissions] ObjPerm

join sys.server_principals Principals

on ObjPerm.sid = Principals.sid

join #Permisiontypes

on ObjPerm.permission_type = #Permisiontypes.id

join internal.folders fold

on fold.folder_id = ObjPerm.object_id

where object_type = 1

union all

SELECT     [Object] =  ‚Environment‘,
Foldername
= fold.name,
Environmentname = env.name,         
[permission_description] = #Permisiontypes.description,
Principals.Name,
[is_deny]

FROM       [internal].[object_permissions] ObjPerm

join sys.server_principals Principals

on ObjPerm.sid = Principals.sid

join #Permisiontypes

on ObjPerm.permission_type = #Permisiontypes.id

join  [catalog].[environments] env

on ObjPerm.object_id = env.environment_id

join catalog.folders fold

on env.folder_id = fold.folder_id

where object_type = 3

order by Object desc,Foldername,
Principals.name,
permission_description

 drop table #Permisiontypes

This gives the following result:

query results 02

 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.