Cross-schema view query possible with different access rights?

Question

I'm currently using SQL Server 2012, and I have a question concerning cross-schema access with different access rights:

Let's say I have two schemas: UserSchema and TableSchema.

TableSchema contains of 2 tables, and this schema is accessible by an administrator user only, for reading, updates, etc.

UserSchema is to be accessible by users with SELECT-rights (or whatever you would call the reading rights). This schema contains a view that is supposed to select data from the two tables in the TableSchema.

My idea is that the users of the UserSchema should have access to the UserSchema only, but not having access to the TableSchema. Will this work? Or will querying the view not work because of not having persmission to read data directly from the tables? Is there a good solution to work around this, or will I have to forget the idea and give the users reading access to the TableSchema as well?

The answer to the main question is probably obvious, but I seem to find a bit different answers when trying to google it, so this is more or less a yes-or-no-question to confirm or refute the idea.

Abstemious

I am pretty sure you need to either grant permissions on "all tables" i.e. the ones being used in the view(s) and the view(s). Or you can use stored procedures and change their execution contexts to workaround this. – Torrietorrin

Answer 1

So long as the owner of each of the schemas is the same, this approach will work fine because the ownership chain is unbroken.

See the following link for more details:

Ownership Chains

For example this will work:

• UsersSchema Owner dbo

• TableSchema Owner dbo

• User1 grant select permission on UsersSchema, deny select permission on TableSchema

• view1 lives in the UsersSchema (selects from Table1)

• table1 lives in the TableSchema

user1 does a select * from UsersSchema.view1 - SQL server checks that user1 has permissions to select from the view, which he does, all good

SQL server then checks who owns the view, dbo SQL server then checks who owns the table the view is wanting data from, also dbo (as dbo owns both schemas) As the ownership chain is unbroken, SQL server now won't bother checking what permissions user1 has on table1 and the data is returned, even if you deny select to user1 on table1 or the TableSchema.

user1 would still be denied access to table1 if he tried to access table1 directly.

Darrel

Excelent, just what I need. Thanks for the answer :) – Abstemious