Grant permission for a view that uses tables form other databases

Question

I know this question is repeated (Grant Select on a view not base table when base table is in a different database), but I have tried the proposed solution, without any luck.

My idea is grant limited access to data in certain databases through an intermediate database in which I create views. I do this to avoid granting specific permission on external tables every time I create a view. This is what I do:

• I have User_A and User_B
• User_A creates DATABASE_A1 and DATABASE_A2
• User_A creates VIEW_A in DATABASE_A1 that reads data from DATABASE_A2
• I set db_dataread role to User_B in DATABASE_A1, so they now can execute VIEW_A
• If User_B tries to execute VIEW_A, receives the message "The server principal 'User_B' is not able to access the database 'DATABASE_A2' under the current security context", which I expected, as the user has no rights on this database
• I then activate 'cross db ownership chaining' in both databases (not sure if I only need doing it in one), with these commands:

ALTER DATABASE Database_A1 SET DB_CHAINING ON;  
ALTER DATABASE Database_A2 SET DB_CHAINING ON;

• I check if chaining is active by running:

SELECT is_db_chaining_on, name FROM sys.databases;

• All looks ok, as the 'is_db_chaining_on' value is set to '1' on both DB's
• I retry executing the view... but keep denying access, same error.

Is there something I'm doing wrong?. I thin I have followed the indications stated in:

Ownership Chaining

cross db ownership chaining Server Configuration Option

Enabling Cross-Database Access in SQL Server

Answer 1

For dbo-owned objects, the database owners need to be the same to maintain an unbroken ownership chain. Make sure the databases have the same owners and use ALTER AUTHORIZATION ON DATABASE::YourDatabase TO YourDbOwner; to remediate. For objects in other schemas, the schema owner in each database needs to map to the same login.

Users also need an account in each database accessed (unless the guest user is enabled). However, no permissions need be granted on indirectly referenced objects.

Answer 2

You need to create the user in the other database (without granting any rights).

USE [DATABASE_A2]
GO
CREATE USER [User_B] FOR LOGIN [User_B]
GO