Block SSRS Developers from embedding SQL Statements in Report

Question

Is there any way I can automatically block SSRS reports with embedded SQL from accessing my Databases?

I want to force a standard practice of using Stored Procedures, so the DBAs can take back control.

I’d like to avoid implementing a Policing solution where daily jobs interrogate the SSRS Reports for SQL Statements.

I want to block users from having Read Permissions on the databases, so here’s my plan:

• All Data Sources use an SSRS service account that has read permissions on designated databases.

• The SSRS service account will be given execute permissions on a per SP basis (so I can audit every SP that is created to support an SSRS report).

• The DBA will be in control of the SP deployment and permissions management.

• Access to the SSRS rdl file is managed via AD, this is the single area where I manage end user permissions. (the user can only see data via an SSRS report)

My problem is that a "Rogue" Developer can modify a report to contain a SQL statement and use the SSRS service account to read data.

If I can’t Block this behaviour then the very busy DBA will have to take ownership of deploying SSRS reports to live.

• Dan Guzman: You could grant permissions on only stored procedures.
• pacreely: @Dan Guzman : I've added more to the question to provide context. I'm hoping to avoid using Signed Stored Procedures, certificates are a rabbit hole I don't want to go down yet.

Answer 1

create a database schema (lets say "reporting"), and add only the stored procedures you want the developer to have access to that schema, grant the allowed users access only to that schema.