February 17, 2017 at 1:05 pm
My goal is to synchronize permissions between two environments. So, I have staple scripts to generate "CREATE LOGIN...", "CREATE USER...", role membership, permissions, etc.at the server and database level.
But where can I find an example of scripting out SSIS catalog permissions??? I have googled all afternoon but can't find where someone has done this.
For example, I would like to script out all the necessary SSIS catalog "GRANT PERMISSION" statements for deployment on another server.
grant_permission [ @object_type = ] object_type
, [ @object_id = ] object_id
, [ @principal_id = ] principal_id
, [ @permission_type = ] permission_type
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 18, 2017 at 5:28 am
Hi,
SSIS Catalogs are maintained via SQL database SSISDB. All the permissions of SSIS catalog folders, environments etc are maintained in table. So you would need to prepare script to provide entries corresponding to these objects in the relevant catalog table. Check out a sample query:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT eop.[object_type]
,eop.[object_id]
,eop.[permission_type]
,iof.object_name
FROM [SSISDB].[catalog].[effective_object_permissions] eop
join [internal].[object_folders] iof on eop.object_id = iof.object_id and eop.object_type=iof.object_type
Would try to get back to you with complete plan later.
February 18, 2017 at 12:19 pm
In SSISDB database, permissions can be granted by making use of stored procedure "catalog.grant_permission". SSIS catalog permission are saved in four kind of objects used in view [internal].[object_permissions]
They are [internal].[folder_permissions], [internal].[project_permissions], [internal].[environment_permissions] and [internal].[operation_permissions]
February 18, 2017 at 12:48 pm
Thanks for finding what tables contain the catalog permissions. Based on this I'll code up a script to generate GRANT_PERMISSION statements from SSISDB. There should have been a way to do this from SSMS.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply