Setting permissions for SP with cross-database join 1

[donutman]

I've granted exec permissions on a SP in database A. The SP references a table in db B. The connection string references db A. The connection fails because it doesn't have rights to the table in db B.
If I change the scenario so that the SP is defined in db B, but still use a connection string to db A, then the connection succeeds. Although I do have to tell the ADO control that the record source is B.dbo.SP. Maybe the reason that works is because the ADO control is smart enough to change the Initial Catalog to db B?
So the above works well, I do NOT have to give explicit table rights by using the SP approach. However, how do I solve the problem of doing a cross-database join within a SP without giving explicit Select rights to the table that is in a different db than the one used in the connection string?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[JamesLean]

This is called "cross-database ownership chaining" and can have security implications if not implemented carefully. For this reason it is disabled by default.

You can enable it for the whole server, or just for the specific databases involved which is the recommended method if you do need to use it.

Look it up in BOL for more info.

--James