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.