Ran into an issue at a client this week that I haven’t run into before.  When trying to access the new R3 power management reports, we were receiving an access denied.  The errors we received were the following:

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset ‘DataSet2’. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset ‘DataSet2’. (rsErrorExecutingCommand)
The EXECUTE permission was denied on the object ‘PowerManagementGetPowerCapabilities’, database ‘SMS_XXX’, schema ‘dbo’.  

All other reports in Reporting Services worked fine.  The reason turned out to be that the new R3 reports use some stored procedures in the ConfigMgr database.  Web reports are typically just queries that use tables and views.  The new R3 reports are the first ones for ConfigMgr that are Reporting Services only reports.  Typically when locking down permissions on reports or a reporting service account, you would just assign the account DB_Reader rights in the SQL database.  This works for all the web reports but didn’t work for the R3 reports.  DB_Reader doesn’t have access to run the stored procedures that are required for the power management reports. 
 
In order for the R3 reports to run successfully, we need to add the service account to the “smsschm_users” database role. Huge thanks to Kent Agerlund and Garth Jones for providing me with the appropriate fix.

First, lets open up the database and drill down to the database roles:

image

Next we need to go into the properties of the "smsschm_users” role:

image

Next we need to add our service account to be a Role Member:

image

Select “Browse” and then select the service account you want to use.

image

Here we have our service account selected, then click “ok”.

image

Once you verified the service account is listed in the Role Members pane, then select “ok”.

image

Another way to add the necessary rights is to go into the login properties of your service account. Then go to “user mapping” and select the SMS/ConfigMgr DB and check the database role membership. This is probably the easier method (less clicks), but it’s good to know there are 2 ways to do it Smile

image

Kent Agerlund also has a post on this.  I wasn’t able to find his post when I encountered the issue and used Google, however I was told that using Bing would have resolved my issue, but I haven’t tested that. 

http://blog.coretech.dk/kea/modifying-the-dataset-execution-account-after-installing-configuraiton-manager-r3/