stored procedure calling data in a different schema

Question

SQL Server 2008 R2: normally, we create our table, and stored procedure, and grant a user execute rights to the stored procedure. We never have to grant specific rights to the tables or views because if they user can execute the stored procedure SQL Server infers that the stored procedure should be allowed to perform the select/insert/update statements. Works well because we're only dealing with one schema, but now we've got a scenario where the tables are in one schema, but a stored procedure is in another. When the user executes the stored procedure, they get an error:

Msg 229, Level 14, State 5, Procedure teststoredprocedure, Line 7 The SELECT permission was denied on the object 'testtable', database 'testdatabase', schema 'testschema'.

teststoredprocedure is in a different schema than testtable. Is it possible to allow the stored procedure to select from tables, without granting the user specific rights to those tables?

Answer 1

You need to give the owner of the stored procedure WITH GRANT access to the table.

Generally a Schema is owned by a Role with the same name as the schema, so to allow storedprocschema.teststoredprocedure to access the table it would be:

GRANT SELECT on testschema.testtable TO storedprocschema WITH GRANT

This should work IF and only if the table is in the same database as the proc.

To achieve the same result with a table in a different database you can either:

• Enable "Cross Database Ownership Chaining"

• Move the procedure to the other database, and have a shim procedure in the original database which calls it. Then manage permissions on both procedures.