CODEWITHSUNDEEP
sqlsql-servert-sqlpermissionsdatabase-schema
J. Mini
J. Mini

Reputation: 1610

What happens if you GRANT permissions on a view, but DENY those same permissions on its schema?

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?

Upvotes: 0

Views: 155

Answers (1)

Charlieface
Charlieface

Reputation: 72298

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.

Upvotes: 1

Related Questions