SQL Cross Database Views Security

Question

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,
       Customersalescode varchar(25),
       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

Answer 1

Unless DB_CHAINING is set to true for all 3 databases, you should give the SELECT permissions on the underlying tables.

In case you set DB_CHAINING to true, all 3 owners of these databases are the same and all 3 objects have the same owner (for now it's not true in your design because your tables are owned by dbo and view is owned by vdr) permissions on the underlying tables will not be checked due to Ownership Chaining

To make ownership chaining work in your case you should do the following:

• Run:

  use Reporting; 
  go 
  alter authorization on schema::vdr to dbo;`
  

• set db_chaining to true on all 3 databases:

  use master;     
  go      
  alter database Reporting set db_chaining on;    
  alter database DatabaseA set db_chaining on;
  alter database DatabaseB set db_chaining on;
  

• check owners of all 3 databases and if it's not the same change it to the same login using

  alter authorization on database::yourDB to yourLogin
  

Answer 2

Read these posts Ownership chaining in SQL Server security feature or security risk and Understanding Cross Database Ownership Chaining in SQL Server.

After you read those, weigh the pros and cons of DB_Chaining. Also, make sure that the consultants or vendors don't make changes to the views that may propagate the tables Modify Data Through a View. I know you're only trying to grant the necessary permissions and access to specific data, but your org has already allowed access to these 3rd parties. I'm thinking the next request will be "Can we change..."

Review your contract and make sure you're not giving more access than necessary.