Imagine that I have a schema FOO. I DENY my mother the SELECT permission on it, but GRANT her it on the view FOO.BAR. What are the consequences of this?

CodePudding user response：

SQL Server permissions work in a similar way to Windows and other permissions: a Deny always overrides a Grant. And a permission to a parent object normally includes its children, so permissions on a schema also apply to its objects.

So if you were to try select from FOO.BAR, the server would aggregate the grants and denies from the database, schema and object levels, leaving it with a GRANT and a DENY. Since the DENY always overrides the GRANT, permission will not be granted.

Note also

• A DENY cannot be placed on an object's owner against that object.
• REVOKE is different from DENY: it removes the GRANT or DENY.