The issue seems to be trying to get a limited access user account the ability to cross databases.
Here's the situation:
We have a User [WebUser] that we want to grant access to the database. This account has a login [WebUser] that has username=WebUser and password=ALongPassword.
This user only calls stored procedures in the database [WebData].
However, some of the stored procedures in [WebData] call stored procedures in the database [dbutil].
One of the stored procedures in [dbutil] inserts records into a table in a third database [dbutil_temp].[DebugLog].
This all works out great from my development account using Windows Authentication.
But as you might guess, if I do something like "EXECUTE AS [WebUser]" and run the same procedure on [WebData] things fall apart quickly. I've looked online regarding cross-database ownership chaining, but quite frankly, the whole users/logins/roles/schemas security model is confusing, and I'm getting nowhere fast on my own.
We really only want [WebUser] to have CONNECT and EXECUTE permissions on the primary [WebData] database, but it seems like we've got to do a lot more than that to get this to work.
I'd appreciate any help...
Yes, you have to do a bit more work, but it isn't a whole lot more work. The most appropriate solution for this is to use signatures for the cross database access. There are detailed demos for creating signatures as well as specifically signatures for cross database queries in the following blogs, which I VERY highly recommend reading.
http://blogs.msdn.com/lcris/
http://blogs.msdn.com/raulga/
I would also suggest listenting to the security presentations at http://cmcgc.com/media/WMP/261115
|||Thanks Mike, I'll take a look at those resources.