All geek questions in one place

Stored procedures calling data in another schema

SQL Server 2008 R2: We usually create our table and stored procedure and grant the user rights to execute the stored procedure. We should never grant specific rights to tables or views, because if they can execute a stored procedure, SQL Server says that the stored procedure should be allowed to execute select / insert / update statements. It works well because we are dealing with only one schema, but now we have a scenario in which tables are in one schema, but the stored procedure is in another. When the user executes the stored procedure, they receive an error:

Msg 229, level 14, state 5, teststoredprocedure procedure, line 7 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 a stored procedure to select from tables without granting specific user rights to these tables?

+6
source share
2 answers

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

Typically, a schema belongs to a role with the same name as the schema, therefore, to allow storedprocschema.teststoredprocedure to access the table, it will:

 GRANT SELECT on testschema.testtable TO storedprocschema WITH GRANT

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

To achieve the same result with a table in another database, you can:

  • Enable "Database Binding Chain"

  • Move the procedure to another database and perform the routing procedure in the source database that calls it. Then manage permissions for both procedures.

+2
source

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

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

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

Conversely, if your database user security context has the necessary permissions, you can also use the shortened version for this:

 with execute as self
0
source

Source: https://habr.com/ru/post/903800/

More articles:


All Articles