SQL Server View permissions need table permission too

Question

We've built a collection of views, under a specific schema as we wanted to set the SELECT permission only to some developers for that particular schema, and perform the same logic for other developers working on data from other schemas. The challenge we're facing now is that, e.g., View_X, under schema Schema_X (owner = dbo), selects data from Table_X under Schema_Y (owner = dbo).

The setup at the moment is built this way:

• We created Role A with Developer_A and Developer_B as members;
• For which we've DENIED all permissions for securable SCHEMA Schema_Y and other schemas, as we only want these members to select data from Schema_X. When Developer_A or Developer_B query View_X, they get the error saying they don't have permissions for Schema_Y

Is there a way to prevent our developers to query Table_X or other tables from Schema_Y, via SQL Server permissions? Or any other way via some other user management logic?

Answer 1

There's something else going on. A DENY won't prevent ownership chains; ownership chains suppress permissions checking entirely. eg

create schema X
go
create schema Y
go
create table Y.tt(id int)
go
create view X.vtt as select * from Y.tt

go

create user xv without login

grant select on schema::X to xv
deny select on schema::Y to xv

go

execute as user='xv'
  select * from X.vtt
revert