Understanding access when there is database chaining

Question

I am new to SQL Server database and I am struggling to figure out the access issue for one of the user on a particular view. I don't want to expose any of my base tables.

The scenario is: I have 3 databases, DB one, two and three

• Database one has 2 base tables
• Database two has one view on top of those tables (tables in database one)
• Database three has one view which is on top of the view of database two

Database three is our data warehouse. So, I would like to know if I give select permission on only database three's view, will that suffice?

The catch is I don't want to expose any of my base tables in database one

If I grant select permission to user1 on datawarehouse view (view in database three) and deny all the permissions to the base tables (in database 1), then is it possible?

Thanks

Answer 1

Ownership chaining allows access to data via the view without permissions on the underlying tables as long as all objects are owned by the same security principal. There is no need for an explicit GRANT or DENY on the indirectly used objects with an unbroken ownership chain since permissions are checked only on the directly access view. The object owner is typically inherited from the schema owner.

To allow ownership chaining to extend across multiple database:

• The DB_CHAINING database option must be ON for the databases involved.

• The user must be able to use the databases (have a user account in each database with CONNECT permissions), although only permissions on directly accessed objects are needed.

• In the case of dbo-owned objects, the databases must be owned by the same login (AUTHORIZATION) since the dbo schema owner is the database owner. For other schemas, the schema owner must map to the same login.

DB_CHAINING should be enabled only when you fully trust highly-privileged users (those with permissions to create database objects).