Cross Database Views:

Hi, We have two databases

Database A: Contains dbo.Customerid table,

Database B: Contains dbo.Customerdescription table,

Database Reporting: has a schema for consultant vendors (vdr), so they can join, but not see security information, (SSN and driverlicenses, etc)

I am seeing a lot of information: What is the optimal, secure way to give access to the consultants for cross-database views?

I am seeing error messages, "The SELECT permission was denied on the object DatabaseA.dbo.customer, etc"

create table DatabaseA.dbo.Customer
(
       Customerid int primary key,
       Membersince datetime
)
create table DatabaseB.dbo.CustomerDescription
(
       CustomerId int primary key, 
       Firstname varchar(255), 
       LastName varchar(255), 
       SSN int,  
       Driverlicensenumber varchar(30)
 )

create view vdr.CustomerReport
as
select 
    cust.CustomerId,
    cust.Membersince,
    cds.FirstName,
    cds.LastName
from DatabaseA.dbo.Customer cust
inner join DatabaseB.dbo.Customerdescription cds
    on cust.Customerid = cds.customerid