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.
Best Answer
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.