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!!