Sfrgttk

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'.

• 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'.