Home Dashboard Directory Help
Search

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


Status: 

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


14
0
Sign in
to vote
Type: Bug
ID: 558417
Opened: 5/11/2010 9:18:37 AM
Access Restriction: Public
1
Workaround(s)
view
7
User(s) can reproduce this bug

Description

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
Sign in to post a comment.
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
Sign in to post a workaround.
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