Logins with the mdw_reader role are unable to view the MDW reports from the Management Data Warehouse report

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

Closed

as Won't Fix Help for as Won't Fix

Type:	Bug
ID:	558417
Opened:	5/11/2010 9:18:37 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

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!

Details (expand)

English

SQL Server 2008 - Standard Edition

Tools (SSMS, Agent, Profiler, Migration, etc.)

Windows 7 Enterprise

US English

Problem repros all releases of SQL Server 2008 and SQL Server 2008 R2.

Create a new login as part of the server public role. Make that user a member of the mdw_reader role for the MDW database.

Connect to the server with the MDW using the new mdw_reader login and navigate to the MDW.

Right click on the MDW database.
Result: No Management Data Warehouse Overview report.

I was able to workaround this problem by granting VIEW DEFINITION rights to the mdw_reader role.

I then was able to run the MDW overview report.

When I click on any of the sub-report hyperlinks, I get an error something like "A data source instance has not been supplied for the data source 'Snapshot Time'.

The mdw_reader doesn't have enough permissions to view the reports as expected from reading the help topic. http://msdn.microsoft.com/en-us/library/bb630341.aspx

mdw_writer Role
Members of the mdw_writer role can upload and write data to the management data warehouse. Any data collector that stores data in the management data warehouse has to be a member of this role.

It turns out that users with the role of mdw_admin and mdw_writer have a similar problem running the reports.

Reports to work for the mdw_reader role - mdw_admin as well

X64

0 attachments

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

Hi

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.
Prachi

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
GO
GRANT VIEW DEFINITION TO [mdw_reader]
GO

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

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

Bill Ramos, Program Manager, Microsoft

Posted by bartduncan on 5/11/2010 at 1:09 PM

use [sysutility_mdw] -- replace this with your MDW DB name
GO
GRANT VIEW DEFINITION TO [mdw_reader]
GO

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

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