Sql-server – SQL Cross Database Views Security

database-designSecuritysql serverview

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

Best Answer

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
Related Question