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

Answer 2

I had exactly this problem but in my case the solution was to update the two databases to have the same owner.

If the databases are owned by the same owner you don't need to explicitly turn on ownership chaining (as the owners are one and the same).

Great article on this here: [http://www.sommarskog.se/grantperm.html][1]

You can update the ownership of a database using sp_changedbowner or the "ALTER AUTHORIZATION" statement for more recent version.