Viewing MDW Reports with a Low Privileged User Account

Just last week, one of our technical sales people asked me the question, when will we release the source RDL files for the Management Data Warehouse (MDW) feature that we shipped with SQL Server 2008. The reason that he asked was that his customer wanted to host the reports on a Reporting Services server so that a low privileged user could view the reports against an MDW shared database hosted on a shared server without actually having to connect to the instance being collected.

I told him, wait – we have a feature that allows you to run the MDW reports on the shared server by opening up the shared server instance in SSMS and then right clicking on the MDW database and selecting the Reports > Management Data Warehouse Overview report. It should be as simple as simply adding the low privileged login account to the mdw_reader role on the MDW database. If you go to the MDSN help topic on Data Collector Security, you’ll see that:

“Members of the mdw_reader role have Read access to the management data warehouse. Because the purpose of this role is to support troubleshooting by providing access to historical data, members of this role cannot view other elements of the management data warehouse schema.”

Great, it’s natural to assume that that’s just what I need to do to run reports. Reports are technically read-only and use the MDW database as it’s source. Well, not exactly.

When I setup my test ‘mdw_user’ SQL login account as a user to the MDW database that I was using – in this case – the new SQL Server 2008 R2 ‘sysutility_mdw’, the first thing I noticed is that the Management Data Warehouse Overview report command was missing! It turns out that the Object Explorer (OE) queries the database for an extended property called ‘Microsoft_DataCollector_MDW_Version’. If OE finds it, it displays the overview report command. It turns out that in order for a user to see extended properties on a database, the need to have VIEW DEFINITION rights. Needless to say, we forgot to add that right to the MDW installation script. The simple workaround is to grant those rights to the mdw_reader role as follows:

-- Allow the mdw_reader and mdw_admin roles to see the MDW overview report in OE
use [sysutility_mdw]
GO

GRANT VIEW DEFINITION TO [mdw_reader]
GO

GRANT VIEW DEFINITION TO [mdw_admin]
GO

Now, you can see the overview report command and run it as shown below.

Security Note: By granting View Definition rights to a database user, the user account has full access to the meta-data for that database. While the database in question is the Management Data Wareshouse, it’s something to consider. Please refer to the help topic – Metadata Visibility Configuration for more information on this permission.

The Overview report looks like this for my setup with two instances uploading data to the shared sysutility_mdw database.

With the overview report, you get a summary of the last upload times for the three system collection sets for each instance that has uploaded to the MDW.  From this point, you can click on the hyperlink for one of the collection sets to view the report. This way you don’t actually have to connect to the target server just to see the same reports that you could run from the Data Collection node.

Well, if you do attempt to run any of the reports with the low privileged user, you are going to end up with one of a couple of insufficient privileges' error message.

This is due to the fact that all of the MDW reports in the footer indicate the state of the data collector so that you can tell if things are running ok. Unfortunately, the queries to see if the data collector are running reply on having access to three views in the system msdb database.

There are two basic workarounds for this. The first workaround is to make the low privileged use a member of either three of the existing dc_admin, dc_operator, or dc_proxy database roles in msdb. The problem is that these roles have more privileges than needed to run the reports.  The second workaround is to created a new role in msdb that I’ll call  ‘dc_report_reader’ and then add the user to that role.  Here is the script that I used.

-- Add a new msdb role for the dc_report_reader. In the future, the MDW wizard would add the login to this new role
-- for any login associated with the mdw_reader, and mdw_admin roles so that they can run reports.
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

Once you run the script for your low privileged user, you can now use all of the canned MDW reports.  I should point out that you also need to do the same with the mdw_admin role. It turns out this role has the same limitations as the mdw_reader role.

The workaround is fairly straight forward and I’ve added the same script to the end of the Data Collector Security help topic as Community Content. If you would like to see this fixed in a future service pack of SQL Server, you can also vote for this item on the SQL Server Connect site at https://connect.microsoft.com/SQLServer/feedback/details/558417/logins-with-the-mdw-reader-role-are-unable-to-view-the-mdw-reports-from-the-management-data-warehouse-report.

As far as the original question about making the MDW reports available via CodePlex, we aren’t ready to do that yet. However, I’m planning to put together a series of blog posts that explore each of the reports and the way they get their data from the MDW over the next couple of months.

In my next post, I’ll start simple and talk about the MDW overview report.

Technorati Tags: SSMS,Data Collector,Management Data Warehouse

Bill Ramos, Program Manager, SQL Server Manageability