Cross-schema view query possible with different access rights?
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:
For example this will work:
UsersSchema Owner dbo
TableSchema Owner dbo
User1 grant select permission on UsersSchema, deny select permission onTableSchema
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, dboSQL 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.