How to Configure a Stored Procedure to Access a Table in Another Database

Question

I have two databases on the same SQL Server:

Database A

• table 1
• table 2
• sproc x

Database B

• table 3
• table 4
• sproc y
• sproc z

I want to give user1 access to the database, but only through the EXECUTE permission on the sprocs.

• sproc x does a join between table 1 & 2, and user1 can execute.
• sproc y does a join between tables 3 & 4, and user1 can execute.
• sproc z does a join between tables 1 & 4, and user1 is unable to execute, unless I grant SELECT permission to table 1. Why is this?

I do not want to grant select permission, because this breaks the security model of "only access the database through sprocs"

Answer 1

You may need to enable cross database ownership chaining for both databases.

To see if it's enabled:

select name, is_db_chaining_on
    from sys.databases

To enable the setting:

EXEC sp_dboption 'DatabaseA', 'db_chaining', 'true';
GO

EXEC sp_dboption 'DatabaseB', 'db_chaining', 'true';
GO