Question on schemas and security

Question

I need to set some users up to be able to create stored procedures with READ access only. Also, in production they cannot have SELECT, only EXECUTE on the procs they created. In development they would have SELECT so they could create their procedures.

I've set up a schema called Reports. The owner of that schema is a login - Report_Admin. That user has select access to tables. I then gave alter and execute on the Reports schema to my report writer account. Dbo owns the table - so it works if dbo also owns the Reporting schema - but then a delete will also work in the procedure!

We are using reporting services and would like to have all the SQL in the database for maintainability.

Thanks!

Answer 1

You can assign permissions to roles and users, no need for separate schema's. So I'd only use one schema: "dbo" (the default)

Create a database role for the users. Grant data_reader to that role on development. On both development and production, grant execute rights on the stored procedures. As far as I know, you'll have to grant the execute right for each stored procedure.

Answer 2

You're not going to succeed using ownership chaining, as you already discovered. A solution would be like this: report_writer must create its reports with an EXECUTE AS SELF clause so they get executed under the report_writer priviledges. Then the report_reader group will be able to leverage the EXECUTE permission on reports schema to execute said reports, and the reports will be able to read the data because of the execute as clause.