One trick veteran report writers like to use when writing SSRS reports for Dynamics CRM is to use a stored procedure for the report query. There are several appealing reasons to do this:

• Performance: Stored procedures are cached, which can give them a performance advantage when compared to a query in the report. This advantage is diminished in modern versions of SQL, but I have seen complex, long running reports where the query timed out when embedded in the report, but completed successfully when using a stored procedure.
• Separation of code: by separating the query in a stored procedure, it can sometimes be easier to troubleshoot/manage the query when it is separated from the RDL. We’ve all seen weird behavior when editing an existing CRM report–you upload it and the old version is cached, so you have to remove and republish the entire report. This can get very complicated if you have multiple subreports. By separating the query into a stored procedure, I can update the query and filters without having to republish the report.
• Reuse of code: Sometimes you need to use the same query for multiple reports. A stored procedure lets me have a single copy of the query used by multiple reports.

There are also some potential downsides to using stored procedures for CRM reports:

• It requires the report writer to have direct access to the CRM SQL database. When using embedded queries in reports, the report writer can author reports from SQL data tools without having direct access to the SQL database. Creating and editing of stored procedures requires a higher level of SQL database privileges than embedded queries.
• It is borderline unsupported. This falls into the same gray area that creating custom indexes does. It is not documented in the implementation guide or the SDK, so it is not officially supported. It also requires elevation of the default database permissions. However, it can be a safe thing to do, as long as your stored procedures work in a supported way: Don’t write directly to the database, don’t query base tables, use filtered views. If you do this, you can remain close to supported with minimal risk.

Enabling stored procedures for reports

So you weigh the pros and cons and decide that you are comfortable with using stored procedures in CRM reports. How do you make it work?

To enable stored procedures for reports, you need to understand how reporting in CRM works. This applies to CRM 2011-2015. When I run a report in CRM, the report is not actually being run by me. Via the SRS data connector, CRM runs the report under the context of the service account that SSRS is running as on the CRM SSRS server.

1. Log on to the SSRS server used by your CRM deployment. Pull up services and find the SQL Server Reporting Services windows service. Go to the properties of the service and look at the “Log on” tab. Look for the service account that the SSRS service runs as.
2. Log on to the SQL server used by your CRM deployment. Go to SQL Management Studio. Add the service account found in step 1 to SQL as a user.
3. Grant the service account “execute” permission to each stored procedure used by your SSRS report.