May 22, 2013 at 7:42 am
How do I grant a regular user or group the same rights to view the SSIS execution reports as say the sysadmin role?
I need to figure out how to give users the visibility of those reports without actually granting sysadmin.
October 23, 2014 at 6:49 am
We have the same question.
We have regional power developers / operators who require access to view their production SSIS 2012 package execution "ALL Executions" report but we do not want to grant sysadmin or ssis_admin to them as they'd be able to view and/or execute SSIS pkg's across the board (including other regions in our company which they should not have access to).
October 23, 2014 at 9:00 am
[font="Courier New"]my alter ego replying to my own question:
I actually found a couple of work arounds to view the SS 2012 SSIS "All Executions" report.
1. modify 2 views -- using this solution: http://sqlwriter.blogspot.com/2013/12/ssiscatalog-report-execution-permission.html --- this is what we opted to use which is described here:
SSIS Catalog "All Executions" Report permission
SSIS developers who do not have ssis_admin access are given db_datareader to SSISDB and public role to server. Still they are not able to view ssis execution reports in SSIS Catalog because ssis_Admin access is required to view them. The access right is limited in Catalog views by Microsoft.
To fix the issues, ALTER the following views in SSIS catalog.
· Catalog.event_messages
· Catalog.executions
To alter catalog.event_messages and catalog.executions, click on script view as > alter to > new query editor window.
Then, comment out the following part of the script.
--comment it to give access to reports
--WHERE opmsg.[operation_id] in (SELECT [id] FROM [internal].[current_user_readable_operations])
-- OR (IS_MEMBER('ssis_admin') = 1)
-- OR (IS_SRVROLEMEMBER('sysadmin') = 1)
After updating views, developer can view executions and drill down messages. Connect to server and you can be able to view reports.
2. deploy a canned set of SSRS reports (requires data source to connect to SSISDB using sa / acct pwd) described here: https://ssisreportingpack.codeplex.com/ --- we've deployed this. cool set of reports[/font]
March 24, 2015 at 8:38 am
Apparently Microsoft has no plans to fix this
This Stack link has an alternate workaround
http://dba.stackexchange.com/questions/78341/permission-to-view-execution-report-in-ssis-catalog
August 2, 2016 at 1:37 pm
Instead of commenting out the roles check in views. I added new role ssis_reports with no additional permissions. Added following to the 2 views mentioned above. Who ever needs access to view reports I added to this role. I think way DBAs can control who can run these reports.
OR (IS_MEMBER('ssis_reports') = 1)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply