Hi,
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_procedu re_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_n ame>", 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?
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_procedu re_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_n ame>", 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?