REVOKE Statement

The REVOKE statement is used to remove database permissions from users or roles.

Syntax


REVOKE [ GRANT | DENY ] { <permission> [<, >]... | ALL [ PRIVILEGES ] }
    ON { [ schema_name . ] object_name | * }
    FROM { <database_principal> [<, >]... }
    [ CASCADE ]
    [ RESTRICT ]

Description

The REVOKE statement removes database permissions. Permissions can be granted or denied to users or roles. The REVOKE statement can be used to undo GRANT or DENY statements.

Parameters

Parameter	Description
`GRANT` \| `DENY`	Specifies whether the permission being revoked was granted or denied. If neither is specified, `GRANT` is assumed.
`<permission>`	The permission to be revoked. This can be a specific permission such as `SELECT`, `INSERT`, `DELETE`, `UPDATE`, `EXECUTE`, etc.
`ALL [ PRIVILEGES ]`	Revokes all permissions that can be granted or denied.
`ON [ schema_name . ] object_name \| *`	The securable object on which to revoke the permission. This can be a table, view, stored procedure, function, or schema. `*` signifies all securable objects of a certain type.
`FROM <database_principal>`	The user or role from which to revoke the permission.
`CASCADE`	Indicates that the permission should also be revoked from principals that inherited the permission from this principal.
`RESTRICT`	Indicates that the permission should not be revoked if it has been granted to other principals.

Examples

Example 1: Revoke SELECT permission from a user on a table


REVOKE SELECT
ON dbo.Customers
FROM AppUser;

Example 2: Revoke all permissions from a role on all tables


REVOKE ALL PRIVILEGES
ON SCHEMA::dbo
FROM ReadOnlyRole
CASCADE;

Example 3: Revoke INSERT permission that was denied to a user


REVOKE DENY INSERT
ON dbo.Orders
FROM DataEntryUser;

Important Note:

When revoking permissions, be cautious with the CASCADE option. Ensure that revoking permissions from a principal does not inadvertently affect other users or roles that rely on those permissions.

Tip:

You can use the sys.database_permissions catalog view to check existing permissions on database objects.

Warning:

Improperly revoking permissions can lead to users losing necessary access to data or functionality, potentially impacting application performance and user experience.

MSDN Documentation