05.12.2014

Managing SSIS Permissions in SQL Server 2012/2014

Technical Value

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.

  1. SELECT
  2.  
  3.  TOP 1000
  4. [folder_id],
  5. [name],
  6. [description],
  7. [created_by_sid]
  8.  
  9. FROM
  10.  
  11. [SSISDB].[internal].[folders]
  12.  
  13. SELECT
  14.  
  15.  TOP 1000
  16. [id],
  17. [sid],
  18. [object_id],
  19. [permission_type],
  20. [is_deny],
  21. [grantor_sid]
  22. FROM
  23.  
  24. [SSISDB].[internal].[folder_permissions]

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:

 

  1. SELECT
  2.  
  3.  TOP 1000
  4. [folder_id],
  5. [name],
  6. [description],
  7. [created_by_sid]
  8.  
  9. FROM
  10.  
  11. [SSISDB].[internal].[folders]
  12.  
  13. SELECT
  14.  
  15.  TOP 1000
  16. [id],
  17. [sid],
  18. [object_id],
  19. [permission_type],
  20. [is_deny],
  21. [grantor_sid]
  22. FROM
  23.  
  24. [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.

 

  1. SELECT
  2.  
  3.  TOP 1000
  4. [folder_id],
  5. [name],
  6. [description],
  7. [created_by_sid]
  8.  
  9. FROM
  10.  
  11. [SSISDB].[internal].[folders]
  12.  
  13. SELECT
  14.  
  15.  TOP 1000
  16. [id],
  17. [sid],
  18. [object_id],
  19. [permission_type],
  20. [is_deny],
  21. [grantor_sid]
  22. FROM
  23.  
  24. [SSISDB].[internal].[folder_permissions]

table #Permisiontypes

  1. (
  2. id
  3.  
  4.  smallint,
  5. description nvarchar(50)
  6. )
  7.  
  8. insert
  9.  
  10.  into #Permisiontypes
  11.  
  12. values
  13.  
  14. (
  15.  
  16. 1,'READ'),
  17.  
  18. (
  19.  
  20. 2,'MODIFY'),
  21.  
  22. (
  23.  
  24. 3,'EXECUTE'),
  25.  
  26. (
  27.  
  28. 4,'MANAGE_PERMISSIONS'),
  29.  
  30. (
  31.  
  32. 100,'CREATE_OBJECTS'),
  33.  
  34. (
  35.  
  36. 101,'READ_OBJECTS'),
  37.  
  38. (
  39.  
  40. 102,'MODIFY_OBJECTS'),
  41.  
  42. (
  43.  
  44. 103,'EXECUTE_OBJECTS'),
  45.  
  46. (
  47.  
  48. 104,'MANAGE_OBJECT_PERMISSIONS')
  49.  
  50.  
  51. /*
  52. Source of Info:
  53. http://msdn.microsoft.com/en-us/library/ff878149.aspx */
  54.  
  55.  
  56. SELECT
  57.  
  58.      [Object] =  'Folder',
  59.  
  60. Foldername
  61.  
  62. = fold.name,
  63.  
  64. Environmentname
  65.  
  66.  = NULL,
  67. [permission_description] = #Permisiontypes.description,
  68. Principals.Name,
  69. [is_deny]
  70. FROM
  71.  
  72.        [internal].[object_permissions] ObjPerm
  73.  
  74. join
  75.  
  76.  sys.server_principals Principals
  77.  
  78. on
  79.  
  80.  ObjPerm.sid = Principals.sid
  81.  
  82.  
  83.  
  84. join
  85.  
  86.  #Permisiontypes
  87.  
  88. on
  89.  
  90.  ObjPerm.permission_type = #Permisiontypes.id
  91.  
  92. join
  93.  
  94.  internal.folders fold
  95.  
  96. on
  97.  
  98.  fold.folder_id = ObjPerm.object_id
  99.  
  100. where
  101.  
  102.  object_type = 1
  103.  
  104. union
  105.  
  106.  all
  107.  
  108. SELECT
  109.  
  110.      [Object] =  'Environment',
  111. Foldername = fold.name,
  112. Environmentname = env.name,         
  113. [permission_description] = #Permisiontypes.description,
  114. Principals.Name,
  115. [is_deny]
  116.  
  117. FROM
  118.  
  119.        [internal].[object_permissions] ObjPerm
  120.  
  121. join
  122.  
  123.  sys.server_principals Principals
  124.  
  125. on
  126.  
  127.  ObjPerm.sid = Principals.sid
  128.  
  129. join
  130.  
  131.  #Permisiontypes
  132.  
  133. on
  134.  
  135.  ObjPerm.permission_type = #Permisiontypes.id
  136.  
  137. join
  138.  
  139.   [
  140.  
  141. catalog].[environments] env
  142.  
  143. on
  144.  
  145.  ObjPerm.object_id = env.environment_id
  146.  
  147. join catalog.folders fold
  148.  
  149. on env.folder_id = fold.folder_id
  150.  
  151. where
  152.  
  153.  object_type = 3
  154.  
  155. order
  156.  
  157.  by Object desc,Foldername,
  158. Principals.name,
  159. permission_description
  160.  
  161. drop
  162.  
  163.  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.

Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden