October 23, 2014 at 6:23 am
[font="Courier New"]
"SSIS 2012 Catalog doesn't have option to give read access to SSIS Catalog to view package run reports"
Has anyone had any luck allowing power developers / operators access to READ the SQL 2012 SSIS Execution Reports without granting them SSIS_Admin or Sysadmin?
According to this link posted back in 2011 (w/ Microsoft's feedback in Nov 2011: "We’re closing this issue as “Won’t Fix.” At this point the bug does not meet our bar for resolving prior to SQL Server 2012 RTM. As we approach the SQL Server 2012 release the bar for making code changes gets progressively higher."
Regarding Permissions to SSIS Catalog, here are the findings. We can give access in three ways:
1. READ Access – We can provide a user db_datareader access. With this the user can see the objects within the SSIS catalog database, but cannot see the reports.
2. SSIS_ADMIN – Add the user to this database role in SSISDB. With this the user can view the reports. But it also provides them privileges to modify catalog information which is not expected. We can add it using below script EXEC sp_addrolemember 'ssis_admin' , 'REDMOND\PAIntelAnalyst'
3. SYSADMIN - Add the user to this server role. This will make the user an admin on the SQL server. This is not intended. Is there any method available which will have provision to give read only access to see SSIS Catalog package execution reports and not having modify Catalog access.
[/font]
October 23, 2014 at 9:01 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]
July 21, 2016 at 7:53 am
This is fixed in SQL Server 2016 with the addition of ssis_logreader role.
It should be noted that all of the catalog schema views are "protected" in this way.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply