SQL Server Interview Question #185

What is GRANT, DENY, and REVOKE?

Security, Backup, Recovery, Administration & Real-World Scenarios Senior Advanced

Detailed Explanation

GRANT provides a permission. DENY explicitly prevents a permission and can override permissions inherited through many other paths. REVOKE removes a previously granted or denied permission entry, returning permission evaluation to other applicable grants, denies, and role memberships.

Because SQL Server permission evaluation can involve multiple principals and scopes, security should be role-based and kept as simple as possible.

Code Example

GRANT SELECT ON dbo.Products TO ReportingRole;
DENY DELETE ON dbo.Products TO ReportingRole;
REVOKE DELETE ON dbo.Products TO ReportingRole;