SQL Server Cross Database Ownership Chaining failing

  • 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

  • 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.

  • Try adding Psychemedics\OnlineServices as a user in the ClientServices database with no permissions. That should allow the stored procedure to work.

    Greg

  • 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