DB Object Access From One Schema to another schema
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 select 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 called sec (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? 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!!
You should be able to do that. You just need to make sure that the owner of the schema of the view has read access to the table(s) in question. As long as that schema owner is different than your login, you should be fine. I usually put all the logins into roles and grant privileges through the roles in order to also have separate schema owners from the actual users within the system. This makes cross-schema security easier.