Published
Jan 15 2019 05:21 PM
3,514
Views
First published on MSDN on Dec 29, 2016
There can be few scenarios where the requirement demands to have Full permission for the developers to have Full access to the SSIS Execution reports. However as per the design SSIS 2012 and SSIS 2014 doesn’t support this it. The non-admin user, by default can see the report which has been executed by them only. They won’t be able to see the reports which have been executed by the other users. Non-admin means they only have public access to all the databases (master, msdb, SSISDB etc.).
Now the Admin users [ either the part of ‘sysadmin’ server role or ssis-admin database (SSIS) role] can see all the SSIS Execution reports for all the users. The SSIS execution reports internally call the view [SSISDB]. [catalog]. [executions]. If we look at the code, we can see that there is a filter condition, which is restricting the non-admin user to see the reports.
WHERE opers.[operation_id] in (SELECT id FROM [internal].[current_user_readable_operations])
OR (IS_MEMBER('ssis_admin') = 1)
OR (IS_SRVROLEMEMBER('sysadmin') = 1)
Resolution / Workarounds:
Ref: https://msdn.microsoft.com/en-us/library/bb522534.aspx#LogReader
The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication. (.Net SqlClient Data Provider).
I believe this option would be risky because we are sharing the admin permission to the non-admin users. Though they won’t be able to execute the report, however they would be able to change the configuration of the report since they have the ssis-admin permission.
[ Please note that Microsoft does not support this solution, as this involves changing the code of the SSISDB views. Also, this change can be overwritten if we apply any patches/fixes ]
a. Let’s create SQL Authenticated Login with minimal permission:
testSSIS for my case:
SQL Server Instance -> Security-> Logins-> New
b. Go to the login->User Mapping under the same login and check the SSISDB database. You can give the read permission as shown below.
c. Create a SSISDB database role in my case SSISTestSSISDBRole and add the testSSIS user.
d. Also, you can add other windows account as the member in this role.
e. Go to the Alter View code and Alter the view by adding one more filter condition at the end. You need to go to the [SSISDB]. [catalog]. [executions] and alter the script.
Change the below filter condition at the end.
WHERE opers.[operation_id] in (SELECT id FROM [internal].[current_user_readable_operations])
OR (IS_MEMBER('ssis_admin') = 1)
OR ( IS_MEMBER ( 'SSISTestSSISDBRole' ) = 1 ) -- Extra filter condition.
OR (IS_SRVROLEMEMBER('sysadmin') = 1)
All the non-admin userss would be able to see the reports for all the Executions . Please note that you would only be able to see the basic reports. The Drill through report will not work for this case.
Testing:
Go to:
NOTE: Microsoft CSS does not support the above workaround. We recommend that you move to SQL Server 2016 and make use of the new ssis_logreader database-level role.
Author: Samarendra Panda – Support Engineer, SQL Server BI Developer team, Microsoft
Reviewer: Krishnakumar Rukmangathan - Support Escalation Engineer, SQL Server BI Developer team, Microsoft
There can be few scenarios where the requirement demands to have Full permission for the developers to have Full access to the SSIS Execution reports. However as per the design SSIS 2012 and SSIS 2014 doesn’t support this it. The non-admin user, by default can see the report which has been executed by them only. They won’t be able to see the reports which have been executed by the other users. Non-admin means they only have public access to all the databases (master, msdb, SSISDB etc.).
Now the Admin users [ either the part of ‘sysadmin’ server role or ssis-admin database (SSIS) role] can see all the SSIS Execution reports for all the users. The SSIS execution reports internally call the view [SSISDB]. [catalog]. [executions]. If we look at the code, we can see that there is a filter condition, which is restricting the non-admin user to see the reports.
WHERE opers.[operation_id] in (SELECT id FROM [internal].[current_user_readable_operations])
OR (IS_MEMBER('ssis_admin') = 1)
OR (IS_SRVROLEMEMBER('sysadmin') = 1)
Resolution / Workarounds:
- The SSIS upgrade to the SSIS 2016 can be an option here. SSIS 2016 brought a new role in the SSISDB, This new ssis_logreader database-level role that you can be used to grant permissions to access the views that contain logging output to users who aren't administrators.
Ref: https://msdn.microsoft.com/en-us/library/bb522534.aspx#LogReader
- If upgrading to SSIS 2016 is not an option, you can use a SQL Authenticated Login to view the report after giving the ssis-admin permission. In that case that SQL Authenticated Login won’t be able to Execute the package, however they would be able to see all the reports. The moment they will try to execute the report, they will get the below error:
The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication. (.Net SqlClient Data Provider).
I believe this option would be risky because we are sharing the admin permission to the non-admin users. Though they won’t be able to execute the report, however they would be able to change the configuration of the report since they have the ssis-admin permission.
- There is one more option by changing the code of the view [SSISDB]. [catalog]. [executions].
[ Please note that Microsoft does not support this solution, as this involves changing the code of the SSISDB views. Also, this change can be overwritten if we apply any patches/fixes ]
a. Let’s create SQL Authenticated Login with minimal permission:
testSSIS for my case:
SQL Server Instance -> Security-> Logins-> New
b. Go to the login->User Mapping under the same login and check the SSISDB database. You can give the read permission as shown below.
c. Create a SSISDB database role in my case SSISTestSSISDBRole and add the testSSIS user.
d. Also, you can add other windows account as the member in this role.
e. Go to the Alter View code and Alter the view by adding one more filter condition at the end. You need to go to the [SSISDB]. [catalog]. [executions] and alter the script.
Change the below filter condition at the end.
WHERE opers.[operation_id] in (SELECT id FROM [internal].[current_user_readable_operations])
OR (IS_MEMBER('ssis_admin') = 1)
OR ( IS_MEMBER ( 'SSISTestSSISDBRole' ) = 1 ) -- Extra filter condition.
OR (IS_SRVROLEMEMBER('sysadmin') = 1)
All the non-admin userss would be able to see the reports for all the Executions . Please note that you would only be able to see the basic reports. The Drill through report will not work for this case.
Testing:
Go to:
NOTE: Microsoft CSS does not support the above workaround. We recommend that you move to SQL Server 2016 and make use of the new ssis_logreader database-level role.
Author: Samarendra Panda – Support Engineer, SQL Server BI Developer team, Microsoft
Reviewer: Krishnakumar Rukmangathan - Support Escalation Engineer, SQL Server BI Developer team, Microsoft