I am attempting to deny a certain schema from a particular User Group. To do this I used the statement "DENY SELECT ON SCHEMA" statement and it was successful in denying access to the tables with that schema.
However, when testing with a view that connected to the denied schema the affected role was still able to pull the data we were attempting deny them.
To help explain, I ran this:
- DENY SELECT ON SCHEMA::[phi] TO [Group1]
And was successful in blocking this:
- select * from phi.NewTable
But this was not blocked (which ran the query immediately above this statement):
- select * from dbo.newview
Is there a way to prevent a view from showing data from a schema that should be denied to someone?