February 3, 2010 at 5:19 pm
I've deleted the text of this post, 'cause my boss thought the it contained some infor about our database that was a little too specific for the internet.
Chuck Bevitt
February 4, 2010 at 6:59 am
I wish I knew the answer to this one. I don't because it looks like your setup is correct and should work.
I will say that I personally would not enable cross-database ownership chaining, I'd sign any stored procedures that need access to another database and I'd be able to control access that way. Here's a blog post about how you can do that.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 4, 2010 at 9:17 am
Try adding Psychemedics\OnlineServices as a user in the ClientServices database with no permissions. That should allow the stored procedure to work.
Greg
February 4, 2010 at 5:26 pm
Figured it out. Many of our stored procedures use dynamic SQL, like {Exec('string query')}. Of course, these execute in a separate batch as the calling user and don't inherit anything from the owner of the procudure.
Rather than give all possible calling users access the the underlying tables in these Exec statements, we decided on a work-around. We created a special SQL Server login/user and gave it db_datareader and db_datawriter role rights on the databases. Then, where needed, we either add the 'With Execute as' option to the declaration of the procedure so that the whole procedure executes as this special user, or we use {Execute as ..} and {Revert} to execute a few statements within the procedure as this special user.
Not perfect, but it works.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply