1

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.

Improve this question
2
  • 2
    You could grant permissions on only stored procedures.
    – Dan Guzman
    Commented Nov 21, 2017 at 15:07
  • @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.
    – pacreely
    Commented Nov 21, 2017 at 15:28

1 Answer 1

Reset to default
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.