During the SQL Reporting Services site system role, SCCM automatically create a login in SQL and assign the required permissions to the account that you have specified. However I have seen several times that the account do not have the permissions required to run some of the Application reports.
When running the Software Distribution – Application Monitoring > All application deployments (advanced) report I get the following error
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset ‘FirstMatchingAppOrCollection’. (rsErrorExecutingCommand)
The EXECUTE permission was denied on the object ‘fnGetSiteNumber’, database ‘CM_PS1’, schema ‘dbo’.
To solve the problem do the followoing:
- Open SQL Server Management Studio and navigate to Databases, CM_<sitecode>.
- Open the database properties and select Permissions.
- Select the Reporting user and assign Execute permissions.
- Click OK and run the report again. This time you can select your applications and collections.
Helpful. I had the same issue – the above solved it. Thank u, Kent 🙂
I had just run into this error as well. Rather than giving execute to the entire database, you can grant execute to just the scalar function fnGetSiteNumber. There are other functions which I wouldn’t want the reporting user to execute. Thanks for this post, it greatly reduced my troubleshooting time!!
This was very helpful. None of the Application Monitoring Reports would run. I ended up granting the smschms database role execute privileges on the fnGetSiteNumber and fnGetSiteNumberbySiteCode scalar functions the execute privilege. I used the database role because the report access account is a member of that role so I covered both accounts that way. Worked like a champ. This article gave me the way to test and I found the additional information about scalar functions here: http://social.technet.microsoft.com/Forums/en-US/859d33a2-b1eb-4975-89e8-37585fd7171f/report-application-deployments-per-asset-fails
Thank you Kent and Anders for sharing this great information!
Very helpful thanks, this has been bothering me since i setup SCCM 2012 🙂
I had this issue as well, and the instructions here resolved the issue – thanks Kent, this page was very helpful. Out of interest, could this be classed as a bug in SCCM?