Error when running Application reports in ConfigMgr 2012

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

image

To solve the problem do the followoing:

  1. Open SQL Server Management Studio and navigate to Databases, CM_<sitecode>.
  2. Open the database properties and select Permissions.
  3. Select the Reporting user and assign Execute permissions.

    image

  4. Click OK and run the report again. This time you can select your applications and collections.

    image

About the Author:

Kent Agerlund
Microsoft Regional Director, Enterprise Mobility MVP. Microsoft Certified Trainer and Principal consultant. I have been working with Enterprise client management since 1992. Co-founder of System Center User Group Denmark in 2009. Certified MCITP: Enterprise Administrator, MCSA+Messaing, and much more. Member of: Microsoft Denmark System Center Partner Expert Team The Danish Technet Influencers program System Center Influencers Program.

5 Comments

  1. Martin May 21, 2013 at 14:32 - Reply

    Helpful. I had the same issue – the above solved it. Thank u, Kent 🙂

  2. Anders Berggren June 13, 2013 at 4:05 - Reply

    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!!

  3. Renee Geffre September 13, 2013 at 19:56 - Reply

    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!

  4. Mark Lee September 23, 2013 at 11:47 - Reply

    Very helpful thanks, this has been bothering me since i setup SCCM 2012 🙂

  5. Rob Talbot November 28, 2013 at 11:47 - Reply

    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?

Leave A Comment