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