DENY (Transact‑SQL)
The DENY statement explicitly denies a permission to a principal. A denied permission overrides any granted permission, ensuring that the principal cannot perform the specified action.
Syntax
DENY permission_name [ ,...n ]
ON { [ object :: ] securable }
TO principal [ ,...n ]
[ AS principal ]
Parameters
- permission_name – The permission to deny (e.g.,
SELECT,INSERT,EXECUTE). - securable – The object or database on which the permission applies.
- principal – The user, role, or application role that receives the denial.
- AS principal – Optional. Specifies the principal that is granting the denial, typically a higher‑privilege principal.
Examples
Example 1 – Deny SELECT on a table
DENY SELECT ON dbo.Employees TO [JaneDoe];
Example 2 – Deny EXECUTE on a stored procedure
DENY EXECUTE ON OBJECT::dbo.usp_GetPayroll TO [SalesRole];
Remarks
- A
DENYtakes precedence over anyGRANTfor the same permission. - When a permission is denied to a role, all members of that role inherit the denial.
- Use
DENYsparingly; it can make permission management complex.