Cross database reference select access with disabled login for DB user

Question

We have two databases (DB1, DB2) with the following view structure.

DB1:

• T1, T2, T3, ..... Tn (Tables)
• V1 (View consuming {T1, T2, T3 ..... Tn })

Inside DB2 we have to create a view V2 which will pull the data from DB1.dbo.V1

USE DB2;
CREATE VIEW dbo.V2 AS SELECT * FROM DB1.dbo.V1; -- with some business logic

Now the problem statement is, user created for the DB2 should not have any login access to DB1 but should be able to login on DB2 to consume the V2.

I don't want to disable the owner chain setting. Till now we have tried the below solution. But it enables the user to see the selected view (V1) inside DB1.

USE DB1;

CREATE View dbo.V1 AS SELECT * FROM T1; -- And some other tables according to business logic

CREATE USER testUser FOR LOGIN testUser;

GRANT SELECT ON dbo.V1 To testUser;

USE DB2;

CREATE View dbo.V2 AS SELECT * FROM DB1.dbo.V1; -- with some business logic

Please consider testUser is already present for DB2, I have just mapped it with the DB1 database.

This solution provides me the expected result. But it also allows testUser to login on the DB1 and fetch the data directly from DB1.dbo.V1. Is there any way to deny login access to DB1 but at the same time allowing select access to V2 created inside the DB2?

What we are trying to do over here is to create an user who will have a complete access to DB2. User should be able to fetch the data using the internal views as explained above. But should not able to login to DB1. In Linux/Unix based systems admin can create an internal user which can be used for the access definitions but can not be used for the logging in into system. We are looking solution to implement something similar in SQL.

If we don't grant select access to V1, server throws permission error:

The SELECT permission was denied on the object V1, database 'DB1', schema 'dbo'.

Answer 1

The nearest I could get was to follow these steps...

Create DB1, t1, t2 And v1 As sysadmin

With a SQL Server Login that has the sysadmin SQL Server Role create the database DB1, the tables t1 and t2 and the view v1. And don't forget database chaining.

/*******************************************
 * Create Database DB1
 *******************************************/
USE [master];
GO
/****** Object:  Database [DB1]    Script Date: 06.07.2021 10:37:39 ******/
CREATE DATABASE [DB1];
GO
USE [DB1];
GO
ALTER DATABASE DB1 SET DB_CHAINING ON;
GO
/*******************************************
 * Create Tables t1 and t2
 *******************************************/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [dbo].t1(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [sometext] [nchar](30) NULL
) ON [PRIMARY];
GO
CREATE TABLE [dbo].t2(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [sometext] [nchar](30) NULL
) ON [PRIMARY];
GO
/*******************************************
 * Create View v1 on t1 & t2
 *******************************************/
 
USE [DB1];
GO
/****** Object:  View [dbo].[view1]    Script Date: 06.07.2021 10:37:11 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE VIEW [dbo].[v1]
AS
SELECT        dbo.t1.ID AS ID1, dbo.t1.sometext AS sometext1, dbo.t2.ID AS ID2, dbo.t2.sometext AS sometext2
FROM            dbo.t1 CROSS JOIN
                         dbo.t2; 
GO
/*******************************************
 * Add a record to each table
 *******************************************/
INSERT INTO t1 VALUES ('test');
INSERT INTO t2 VALUES ('test');

Create DB2 And v2 As sysadmin

With a SQL Server Login that has the sysadmin SQL Server Role create the database DB2 and the view v2. And don't forget database chaining.

/*******************************************
 * Create Database DB2
 *******************************************/
USE [master];
GO
/****** Object:  Database [DB1]    Script Date: 06.07.2021 10:37:39 ******/
CREATE DATABASE [DB2];
GO
USE [DB2];
GO
ALTER DATABASE DB2 SET DB_CHAINING ON;
GO
USE [DB2]
GO
/*******************************************
 * Create View v2
 *******************************************/

CREATE VIEW [dbo].[v2]
AS
SELECT        *
FROM            DB1.dbo.v1
GO

Create SQL Server Login dbuser1

Create a SQL Server Login dbuser1 and link as database user to DB1 and DB2. Assign the db_role db_owner in both databases. Additionally change the owner of the dbo schema to be owned by dbuser1 in both databases.

USE [master]
GO
CREATE LOGIN [dbuser1] WITH PASSWORD=N'dbuser1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
/* 
  Create Database User dbuser1 in DB1
*/
USE [DB1]
GO
CREATE USER [dbuser1] FOR LOGIN [dbuser1]
GO
ALTER USER [dbuser1] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_owner] ADD MEMBER [dbuser1]
GO
/*
  Change the ownership of the database role dbo to dbuser1
*/
use [DB1]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [dbuser1]
GO
GRANT TAKE OWNERSHIP ON SCHEMA::[dbo] TO [dbuser1] WITH GRANT OPTION 
GO
/*
  Create Database User dbuser1 in database DB2
*/
USE [DB2]
GO
CREATE USER [dbuser1] FOR LOGIN [dbuser1]
GO
ALTER USER [dbuser1] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_owner] ADD MEMBER [dbuser1]
GO
ALTER ROLE [db_datareader] ADD MEMBER [dbuser1]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [dbuser1]
GO
/*
  Change the ownership of the database role dbo to dbuser1
*/
USE [DB2]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [dbuser1]
GO
GRANT TAKE OWNERSHIP ON SCHEMA::[dbo] TO [dbuser1] WITH GRANT OPTION 
GO

Now we have a user dbuser1 that owns both databases and the default schema dbo in each database.

Create SQL Server Login dbuser2

Create a SQL Server Login dbuser2 and link as database user to DB1 and DB2, but without any permissions. It's public (default) access only.

USE [master]
GO
CREATE LOGIN [dbuser2] WITH PASSWORD=N'dbuser2', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [DB2]
GO
CREATE USER [dbuser2] FOR LOGIN [dbuser2] WITH DEFAULT_SCHEMA=[dbo]
GO

USE [DB1]
GO
CREATE USER [dbuser2] FOR LOGIN [dbuser2] WITH DEFAULT_SCHEMA=[dbo]
GO

The user dbuser2 is allowed to view the databases, but otherwise does not have much permissions/privileges. The user can not SELECT from any tables or views.

Grant Database User dbuser2 SELECT Permission

Now we are going to grant dbuser2 the SELECT privileges on the view v2 in the database DB2. We will do this while logged in as dbuser1. (Or with the EXEUCTE AS... workaround)

EXECUTE AS USER = 'dbuser1'
go
use [DB2]
GO
GRANT SELECT ON dbo.v2 TO [dbuser2]
GO

Check Permissions

Open a new SQL Query and connect as dbuser2 with the password dbuser2. Enter the following commands to verify the security is working:

USE DB2
go
SELECT * FROM dbo.v2

This will return a result set:

ID1 | sometext1                      | ID2 | sometext2                     
--: | :----------------------------- | --: | :-----------------------------
  1 | test                           |   1 | test                          

The next statement will fail.

SELECT * FROM DB1.dbo.v1;

Msg 229, Level 14, State 5, Line 7
The SELECT permission was denied on the object 'v1', database 'DB1', schema 'dbo'.

Summary

We have achieved a situation where dbuser2 has been granted the SELECT permission by dbuser1 on the view DB2.dbo.v2 that is based on the view DB1.dbo.v1, but even then dbuser2 can not access DB1.dbo.v1 directly.

^{Reference: Ownership Chains (Microsoft | Docs)
Reference: Tutorial: Ownership Chains and Context Switching (Microsoft | SQL Docs) newer article}

Answer 2

• Make sure DB_CHAINING is ON.
• Map TestUser Login to DB1 and DB2.
• Grant SELECT on V2.
• Don't grant SELECT on V1.

See Ownership Chains in the documentation.

Basically, what it does is that, because V1 and V2 has the same owner (that's mandatory) and because DB_CHAINING is ON, when TestUser will SELECT V2, permissions will not be checked on V1, so you don't need to grant it any permission.

TestUser will be able to retrieve data from V2 but SELECT on V1 will be denied.

Double-check that DB_CHAINING is ON by executing:

SELECT name, is_db_chaining_on 
FROM sys.databases;

If it's not the case, set it with:

ALTER DATABASE DBX SET DB_CHAINING ON

...and make sure both views have the same owner.

You can try following code to validate that it's working:

Connect as sysadmin and create Databases DB1 and DB2

USE master
GO
CREATE DATABASE DB1;
GO
CREATE DATABASE DB2;
GO

in DB1:

Create a sample table TB1

Insert a value in TB1 table (this is only because I dont like empty result set)

Create a sample view selecting all rows in TB1 table

USE DB1
GO
CREATE TABLE TB1 (id int);
GO
INSERT INTO TB1 VALUES(42);
GO
CREATE VIEW V1 AS SELECT * FROM TB1;
GO

in DB2:

Create a sample view V2 selecting all rows in DB1.dbo.V1 view

USE DB2
GO
CREATE VIEW V2 AS SELECT * FROM DB1.dbo.V1;
GO

Create TestUser Login

USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

in DB1:

Create TestUser User mapped to TestUser Login

USE [DB1]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO

in DB2:

Create TestUser User mapped to TestUser Login

Grant SELECT on V2 view to TestUser user

USE [DB2]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
GRANT SELECT ON V2 TO TestUser;

Connect with TestUser Login

In DB2:

Try to select from V2

USE DB2
GO
SELECT * FROM V2;

This should fail with error message: The SELECT permission was denied on the object 'V1', database 'DB1', schema 'dbo'.

Connect as sysadmin

Enable DB_CHAINING on DB1

USE master
GO
ALTER DATABASE DB1 SET DB_CHAINING ON;
GO

Enable DB_CHAINING on DB2

ALTER DATABASE DB2 SET DB_CHAINING ON;
GO

Connect with TestUser Login

In DB2:

Try to Select From V2 again, that time it should succeed

USE DB2
GO
SELECT * FROM V2;

In DB1:

Try to Select From V1

USE DB1
GO
SELECT * FROM V1;
GO

As we didnt grant TestUser any permission this will fail with error message: The SELECT permission was denied on the object 'V1', database 'DB1', schema 'dbo'.