Logins with the mdw_reader role are unable to view the MDW reports from the Management Data Warehouse report - by The Slacker DBA

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 558417 Comments
Status Closed Workarounds
Type Bug Repros 7
Opened 5/11/2010 9:18:37 AM
Access Restriction Public


When a user is connected to the instance of SQL Server that has an MDW on it and they are a member of the mdw_reader role for the MDW, the following problems exist.
1. When the user right clicks on the MDW and selects the Reports menu, the Management Data Warehouse report is not displayed.
2. If you work around problem 1 by granting view definition rights to the mdw_reader, when the user clicks on any of the three collection set reports, an insufficient permissions error is raised.

The help topic suggests that users that have mdw_reader rights can view data in the MDW. The problem is, they can't view the data using the MDW reports!
Sign in to post a comment.
Posted by ob213 on 7/17/2014 at 2:19 PM
This is still a problem in SQL Server 2014.
Posted by Janos Berke on 3/19/2013 at 1:27 PM
This is still a problem after several updates as well as major release. :S
Posted by Microsoft on 6/3/2011 at 10:29 AM

Thanks for writing in to Microsoft.

We have given this suggestion a lot of consideration. However, given the work that would be involved in implementing this suggestion, and triaging against our current set of deliverables, we do not think that we would be able to get to this in the near future.

However, we value your suggestions, and assure you that we would keep these ideas in mind, when we do revisit this feature in the future. In the meantime, kindly use the earlier workaround suggested by Bill.

Thank you for your valuable feedback.
Posted by Bill Ramos on 5/11/2010 at 12:30 PM
In digging into this problem, the MDW reports access several collection set views in msdb. The mdw_reader and mdw_admin roles apply only to the actuall MDW. The workaround is to create a new role in msdb that you can add the login/user to so that mdw_reader and mdw_admin user can run all reports. Here is the workaround script.

-- Allow the mdw_reader/admin role to see the MDW overview report in OE.
use [sysutility_mdw] -- replace this with your MDW DB name

-- Add a new msdb role for the dc_report_reader
USE [msdb]
CREATE ROLE [dc_report_reader] AUTHORIZATION [dbo]
GRANT SELECT ON [dbo].[syscollector_collection_sets] TO [dc_report_reader]
GRANT SELECT ON [dbo].[syscollector_execution_log] TO [dc_report_reader]
GRANT SELECT ON [dbo].[syscollector_config_store] TO [dc_report_reader]

-- For each mdw_reader like user, you need to add them to the dc_report_reader role in MSDB
USE [msdb]
CREATE USER [mdw_user] FOR LOGIN [mdw_user]
EXEC sp_addrolemember N'dc_report_reader', N'mdw_user'

Bill Ramos, Program Manager, Microsoft