How to restrict underlying table access used for a view ?

Question

HI all,
I've got following setup on SQL Server 2016.
I have 2 tables created on "GEN" schema on a Database.
There is a view( Let's say "vwEmp") on "HR" schema on the same database, and the view is created using 2 tables in above mentioned "GEN" schema.

For "vwEmp", I provided the view permission set to a specific windows 'HR group'. ( Right click "vwEmp" > Properties > Permissions > then added the 'HR group' > tick only Grant 'select' box )

But the issue is when users in the above 'HR group' try to open the view they get a message as they cannot open the view as they don't have access to "GEN" schema.

Is there a way to handle the above scenario pls? , What I want let 'HR group' open the view, and restrict access to tables on "GEN" schema ?

Thanks in advance.

Answer 1

This is a duplicate thread with https://learn.microsoft.com/answers/questions/788483/how-to-restrict-display-of-underlying-tables-of-a.html. For your convenience, I repeat my answer:

It seems like the view and the tables have different owners. If that is the case, the users need to have permission to access the underlying tables.

On the other hand, if the view and the tables have the same owner, SQL Server employs something that is known as ownership chaining. In this case, SQL Server does not perform any permission check at all on the objects accessed by the view. The assumption is that the owner of has made a decision of what to expose of the underlying tables.

No such assumption can be made if the owners are different, because if Alice defines a view over Bob's tables, Bob may not agree with what Alice expose to other users.

Very commonly, all objects in a database are owned by dbo, but it seems that you have for some reason decided not to adhere to this pattern.

Answer 2

Thank you ErlandSommarskog.
Will do changes and update.
Thanks.
Mira