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?

  • What permissions are present on the view?
    – Hannah Vernon
    Mar 6 '17 at 23:19
  • 1
    This is ownership chaining. If you give permissions on one object that references anyother with the same owner permissions aren't checked on the 2nd. So don't give permissions on the view or ensure they have different owners. Mar 7 '17 at 6:39
  • IN other words, if newview was also in the phi schema, then the users in question wouldn't be able to query it. If newview must be in the dbo schema, and the users in question must be able to access other things in the dbo schema, then access to the view must be explicitly restricted.
    – RDFozz
    Mar 7 '17 at 15:34
  • Actually, @RDFozz, if memory serves, it's the owner, not the schema that has to be the same for ownership chaining to happen. Schema and owner used to be the same thing, way back when. Mar 8 '17 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy