April 28, 2015 at 1:11 pm
Hello experts,
I need help with some confusion I have over stored procedures. I have heard that it is a best practice to use stored procedures to encapsulate some SQL statements and then grant permissions to execute the stored procedure. But when I try this and use EXECUTE AS to test it out, the user in question gets errors about not having access to some of the underlying objects.
How does this best practice work? If I need to grant the user permissions to the underlying objects anyway, I am not sure why a stored procedure is considered best practice in this regard.
I can provide some examples of what I mean if necessary, but I think the general question is clear from what I wrote above. If not, just let me know.
Thanks for any help,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 28, 2015 at 1:37 pm
webrunner (4/28/2015)
Hello experts,I need help with some confusion I have over stored procedures. I have heard that it is a best practice to use stored procedures to encapsulate some SQL statements and then grant permissions to execute the stored procedure. But when I try this and use EXECUTE AS to test it out, the user in question gets errors about not having access to some of the underlying objects.
How does this best practice work? If I need to grant the user permissions to the underlying objects anyway, I am not sure why a stored procedure is considered best practice in this regard.
I can provide some examples of what I mean if necessary, but I think the general question is clear from what I wrote above. If not, just let me know.
Thanks for any help,
webrunner
Without being able to see what you see, hard to say. It would help to se the DDL for the stored procedure and the underlying tables. Also, are all the tables in the same database and/or schema as the stored procedure?
April 28, 2015 at 1:38 pm
Most of the time, it's true, but it depends on your procedure code. Does it access objects in another database? does it contain dynamic SQL queries (EXEC 'select ...' or sp_sqlexecute calls') ?
April 28, 2015 at 1:43 pm
Ah thanks for your answers.
Yes, in this case the code does access different databases. I guess that is the issue. Do you have some general article or web references for how to handle cross-database stored procedures? Does that involve ownership chaining?
Thanks again!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 28, 2015 at 1:50 pm
webrunner (4/28/2015)
Ah thanks for your answers.Yes, in this case the code does access different databases. I guess that is the issue. Do you have some general article or web references for how to handle cross-database stored procedures? Does that involve ownership chaining?
Thanks again!
- webrunner
No links I can remember off hand but the user needs to be in both databases and have direct access to the underlying tables.
April 28, 2015 at 1:51 pm
Lynn Pettis (4/28/2015)
webrunner (4/28/2015)
Ah thanks for your answers.Yes, in this case the code does access different databases. I guess that is the issue. Do you have some general article or web references for how to handle cross-database stored procedures? Does that involve ownership chaining?
Thanks again!
- webrunner
No links I can remember off hand but the user needs to be in both databases and have direct access to the underlying tables.
Thanks, Lynn, I think this is a good lead for now.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 28, 2015 at 2:01 pm
It's better to stay away from database owner chaining.
In your situation, you can grant permissions on underlying objects in other database to users executing the procedure, or you can sign your procedure with a certificate to avoid allowing users to access underlying objects.
If you want to learn more about procedure signing, this should help: https://www.sqlskills.com/blogs/jonathan/certificate-signing-stored-procedures-in-multiple-databases/
April 28, 2015 at 2:07 pm
Eric Prévost (4/28/2015)
In your situation, you can grant permissions on underlying objects in other database to users executing the procedure, or you can sign your procedure with a certificate to avoid allowing users to access underlying objects.
If you want to learn more about procedure signing, this should help: https://www.sqlskills.com/blogs/jonathan/certificate-signing-stored-procedures-in-multiple-databases/%5B/quote%5D
Thanks, Eric!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 28, 2015 at 4:34 pm
Eric Prévost (4/28/2015)
In your situation, you can grant permissions on underlying objects in other database to users executing the procedure, or you can sign your procedure with a certificate to avoid allowing users to access underlying objects.
If you want to learn more about procedure signing, this should help: https://www.sqlskills.com/blogs/jonathan/certificate-signing-stored-procedures-in-multiple-databases/%5B/quote%5D
Making the url more usable:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply