2

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

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

  • The question says, "Is there any way to deny login access to DB1 but…". By suggesting, "Map TestUser Login to DB1 and DB2", your answer appears to fail that specific requirement. If you mean to say there's no way to solve the problem while satisfying it, please consider being explicit about that, otherwise it just looks as though you've overlooked a part of the question. – Andriy M Nov 24 '16 at 14:18
  • You're right, I realize that I answered to what i thought was asked "between lines" ie Make TestUser able to query V2 but unable to query V1 or any DB1 objects. That's my bad. I don't know any way to solve this without having TestUser in DB1, but I can't say there is no way. If it happens that I'm not answering the question I will delete my answer. – Tametale Nov 24 '16 at 14:31
  • You can explicitly deny the connect priviledge to a user: use <db>; DENY CONNECT TO <user>;, but if that then still satisfies the DB1-DB2 View chaining eludes me... – til_b Nov 25 '16 at 9:59
  • 1
    DB chaining will fail if TestUser is denied connect on DB1. – Tametale Nov 25 '16 at 10:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.