Scenario:
I have denied permissions from table1 for USER1.
DENY SELECT,INSERT,UPDATE,DELETE ON OBJECT :: dbo.table1 TO USER1
Instead, I have allowed access to a view(vw_table1) which will selects from table1 and filter some data using where clause. The idea is, users won't be able to see entire data of the table. To support insert, update and delete, I have created instead of triggers.
The above scenario works fine when Table1 and VW_table1 are on same schema.
Now, I have a requirement to move the view only in a different schema (sec.vw_table1). After moving the view when I do "select * From sec.vw_table1" It gives access denied error when the view tries to access dbo.table1.
How can we achieve this in SQL Server? I don't want to give access to dbo.Table1 to User1. But should work when it is called from sec.vw_table1.
Any help would be highly appreciated!!
Best Answer
The concept you want to understand is called "ownership chaining"
As long as the owner of the object and database remain the same, SQL Server will not re-evaluate the permissions of a user after the 1st object in the chain.
In your first example you denied USER1 access to TABLE1 but allowed access to VW_TABLE1. Since USER1 has access to VW_TABLE1 and the ownership chain has not been broken, they can see TABLE1 because permissions are not re-evaluated inside the VIEW.
In your second example I'm assuming the SEC schema must have a different owner which causes SQL Server to re-evaluate permissions when inside the VW_TABLE1 view.
In short, to fix this problem make sure all objects have the same owner