CODEWITHSUNDEEP
sql-serverstored-proceduressql-server-2008-r2
Jeremy
Jeremy

Reputation: 46350

stored procedure calling data in a different schema

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?

Upvotes: 6

Views: 10540

Answers (2)

Ben
Ben

Reputation: 35613

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.

Upvotes: 2

user596075
user596075

Reputation:

Yes, it is possible. Here's what you want to do:

alter procedure teststoredprocedure
with execute as 'UserWithPermissions'
-- rest of stored proc code

Where UserWithPermissions has the necessary permissions on your database objects that you are trying to execute CRUD operations again.

Conversely, if your database user security context has the necessary permissions, you can use the shorthand to achieve this as well:

with execute as self

Upvotes: 0

Related Questions