Does a user who has permissions to select from a view also need select granted to the children entities of that view?

Question

If view 1 uses tables A and B, and a user has SELECT granted on view 1 is that sufficient for the user to select from view 1 or does he also need SELECT granted on tables A and B.

Specific details on my situation (in regards to Erik's comment):

I thought I understood permissions, but I'm a little confused right now.

I have a user specifically for testing permissions. I have view 1 in database 1 that references view 2 in database 2 and view 3 in database 2. View 2 in database 2 references table A in database 3. View 3 in database 2 references table B in database 2.

So at the individual entity level, I slowly was adding permissions as necessary and check with my test user to see what error I get back from SSMS.

I first granted select permissions to the test user on view 1. Then based on the errors I was getting, I also needed to grant select on view 2 and table A (that is referenced by view 2). But I did NOT need to grant select permissions to my test user on view 3. This is where I'm confused.

I then assumed my test user might be part of a role (or something) that gives it full select access on database 2, but then if I try to select directly from view 3 it gets a permission denied error, even though it CAN select from view 1 (which references view 3).

TL;DR: Why can my test user account select from a view but can't directly select from one of the entities that that view is dependent on?...is that normal permissions behavior?

Answer 1

Generally speaking, sub-objects owned by the same owner of the object that one has permission to will pass along that permission. This is Ownership Chaining. By default this behavior is constrained to working within the current database, and with non-dynamic SQL. When you start involving other databases and/or dynamic SQL, then you need some additional configuration to allow that to work (that is, if you don't want to grant direct access to those sub-objects).

The simple / easy mechanisms are to enable Cross-Database Ownership Chaining and/or TRUSTWORTHY (database property), depending on what you are trying to do. However, being simple and easy also means that they both open up rather large security holes.

Fortunately, there is a way to accomplish this with just a little bit of extra work, but the end result will be a much more secure setup. You want to use Module Signing. The only down-side is that you might need to change 1 or more of the Views into Multi-statement Table-valued Functions so that the object can be signed (Views and Inline TVFs cannot be signed). However, the security itself is much cleaner since it is highly specific and self-contained. You will create Certificate-based Users that have the permissions that you want, in each DB that you want them to be extended to (this is done by re-creating the same Certificate in each DB). Then you just need to grant the actual Login / User the EXECUTE permission to the main object.

For a full example of how to do this, please see my answers here:

1. Stored procedure security with execute as, cross database queries, and module signing
2. Access view based on table in another database without account in that other database
3. Permissions in triggers when using cross database certificates

And for more info on why you should use Module Signing instead of Cross-DB Ownership Chaining and/or TRUSTWORTHY, please see my post here:

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining