471,066 Members | 990 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,066 software developers and data experts.

Cross-database ownership chaining in SQL 2005


I have a stored procedure on database A (owner dbo) which is itself owned by dbo. It tries to do a SELECT from a table in database B (both table and db owned by dbo) but fails with:

"Msg 229, Level 14, State 5, Procedure <stored_procedure_name>, Line 210
The SELECT permission was denied on the object '<table_name>', database 'B', schema 'dbo'"

Cross-database ownership chaining is enabled on both databases, and what's strange is that this executes properly in SQL 2000. The only thing I can come up with is that the code selects the table as "B..<table_name>" instead of "B.dbo.<table_name>", i.e. without explicitly specifying dbo schema. However, I thought that this would result only in it checking the schema for the specific user first, and then to dbo.

I can't seem to get this to work unless I grant specific SELECT permissions for each user or role that can access the stored procedure in database A also to the table in database B.

Does anyone have an answer?
May 29 '07 #1
0 1427

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by rollasoc | last post: by
7 posts views Thread by Scott M. | last post: by
3 posts views Thread by jlamanna | last post: by
1 post views Thread by Rob Woodworth | last post: by
6 posts views Thread by Bart Van der Donck | last post: by
6 posts views Thread by ampo | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.