REVOKE (Transact-SQL)

  • Commodity
  • vi minutes to read

Applies to: yesSQL Server (all supported versions) YesAzure SQL Database YesAzure SQL Managed Instance yesAzure Synapse Analytics yesAnalytics Platform Arrangement (PDW)

Removes a previously granted or denied permission.

Topic link icon Transact-SQL Syntax Conventions

Syntax

              -- Syntax for SQL Server and Azure SQL Database      -- Simplified syntax for REVOKE   REVOKE [ GRANT OPTION FOR ]         {            [ ALL [ PRIVILEGES ] ]           |                   permission [ ( column [ ,...n ] ) ] [ ,...n ]         }         [ ON [ class :: ] securable ]          { TO | FROM } primary [ ,...n ]          [ CASCADE] [ AS principal ]                          
              -- Syntax for Azure Synapse Analytics and Parallel Data Warehouse      REVOKE        <permission> [ ,...n ]       [ ON [ <class_type> :: ] securable ]        [ FROM | TO ] principal [ ,...n ]       [ Pour ]   [;]      <permission> ::=   { see the tables beneath }      <class_type> ::=   {         LOGIN       | DATABASE       | OBJECT       | Function       | SCHEMA       | USER   }

Arguments

GRANT OPTION FOR
Indicates that the ability to grant the specified permission will be revoked. This is required when y'all are using the CASCADE argument.

Important

If the principal has the specified permission without the GRANT option, the permission itself will be revoked.

ALL
Applies to: SQL Server 2008 and after

This selection does not revoke all possible permissions. Revoking ALL is equivalent to revoking the following permissions.

  • If the securable is a database, ALL ways Fill-in DATABASE, BACKUP LOG, CREATE DATABASE, CREATE DEFAULT, CREATE Function, CREATE Procedure, CREATE RULE, CREATE TABLE, and CREATE VIEW.

  • If the securable is a scalar function, ALL means EXECUTE and REFERENCES.

  • If the securable is a table-valued function, ALL means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

  • If the securable is a stored procedure, ALL means EXECUTE.

  • If the securable is a table, ALL ways DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

  • If the securable is a view, ALL means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

Note

The REVOKE ALL syntax is deprecated. This characteristic will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Revoke specific permissions instead.

PRIVILEGES
Included for ISO compliance. Does non alter the behavior of ALL.

permission
Is the proper name of a permission. The valid mappings of permissions to securables are described in the topics listed in Securable-specific Syntax later in this topic.

cavalcade
Specifies the name of a column in a table on which permissions are being revoked. The parentheses are required.

class
Specifies the class of the securable on which the permission is existence revoked. The scope qualifier :: is required.

securable
Specifies the securable on which the permission is existence revoked.

TO | FROM chief
Is the name of a principal. The principals from which permissions on a securable tin be revoked vary, depending on the securable. For more data nearly valid combinations, see the topics listed in Securable-specific Syntax later in this topic.

Pour
Indicates that the permission that is being revoked is also revoked from other principals to which information technology has been granted past this primary. When y'all are using the Cascade argument, you must also include the GRANT OPTION FOR statement.

Caution

A cascaded revocation of a permission granted WITH GRANT Choice will revoke both GRANT and DENY of that permission.

Equally principal
Utilize the AS chief clause to indicate that you are revoking a permission that was granted past a primary other than you. For case, presume that user Mary is principal_id 12 and user Raul is principal_id 15. Both Mary and Raul grant a user named Steven the same permission. The sys.database_permissions tabular array will indicate the permissions twice but they will each have a dissimilar grantor_principal_id value. Mary could revoke the permission using the Equally RAUL clause to remove Raul's grant of the permission.

The use of Every bit in this statement does non imply the ability to impersonate some other user.

Remarks

The full syntax of the REVOKE statement is complex. The syntax diagram in a higher place was simplified to draw attending to its construction. Complete syntax for revoking permissions on specific securables is described in the topics listed in Securable-specific Syntax after in this topic.

The REVOKE statement can be used to remove granted permissions, and the DENY statement tin can exist used to preclude a main from gaining a specific permission through a GRANT.

Granting a permission removes DENY or REVOKE of that permission on the specified securable. If the aforementioned permission is denied at a college scope that contains the securable, the DENY takes precedence. However, revoking the granted permission at a higher scope does not take precedence.

Circumspection

A table-level DENY does non have precedence over a column-level GRANT. This inconsistency in the permissions hierarchy has been preserved for backward compatibility. It will be removed in a future release.

The sp_helprotect system stored procedure reports permissions on a database-level securable

The REVOKE argument will fail if Pour is non specified when y'all are revoking a permission from a chief that was granted that permission with GRANT OPTION specified.

Permissions

Principals with Command permission on a securable can revoke permission on that securable. Object owners tin can revoke permissions on the objects they own.

Grantees of CONTROL SERVER permission, such as members of the sysadmin fixed server role, can revoke whatsoever permission on any securable in the server. Grantees of Control permission on a database, such as members of the db_owner stock-still database role, tin can revoke any permission on any securable in the database. Grantees of Control permission on a schema can revoke whatsoever permission on any object inside the schema.

Securable-specific Syntax

The following tabular array lists the securables and the topics that describe the securable-specific syntax.

Securable Topic
Application Role REVOKE Database Chief Permissions (Transact-SQL)
Associates REVOKE Assembly Permissions (Transact-SQL)
Asymmetric Key REVOKE Asymmetric Cardinal Permissions (Transact-SQL)
Availability Group REVOKE Availability Group Permissions (Transact-SQL)
Certificate REVOKE Certificate Permissions (Transact-SQL)
Contract REVOKE Service Broker Permissions (Transact-SQL)
Database REVOKE Database Permissions (Transact-SQL)
Endpoint REVOKE Endpoint Permissions (Transact-SQL)
Database Scoped Credential REVOKE Database Scoped Credential (Transact-SQL)
Full-text Catalog REVOKE Total-Text Permissions (Transact-SQL)
Full-Text Stoplist REVOKE Full-Text Permissions (Transact-SQL)
Office REVOKE Object Permissions (Transact-SQL)
Login REVOKE Server Principal Permissions (Transact-SQL)
Bulletin Type REVOKE Service Broker Permissions (Transact-SQL)
Object REVOKE Object Permissions (Transact-SQL)
Queue REVOKE Object Permissions (Transact-SQL)
Remote Service Binding REVOKE Service Broker Permissions (Transact-SQL)
Office REVOKE Database Principal Permissions (Transact-SQL)
Route REVOKE Service Broker Permissions (Transact-SQL)
Schema REVOKE Schema Permissions (Transact-SQL)
Search Property List REVOKE Search Property List Permissions (Transact-SQL)
Server REVOKE Server Permissions (Transact-SQL)
Service REVOKE Service Broker Permissions (Transact-SQL)
Stored Procedure REVOKE Object Permissions (Transact-SQL)
Symmetric Key REVOKE Symmetric Central Permissions (Transact-SQL)
Synonym REVOKE Object Permissions (Transact-SQL)
System Objects REVOKE Arrangement Object Permissions (Transact-SQL)
Table REVOKE Object Permissions (Transact-SQL)
Type REVOKE Blazon Permissions (Transact-SQL)
User REVOKE Database Main Permissions (Transact-SQL)
View REVOKE Object Permissions (Transact-SQL)
XML Schema Collection REVOKE XML Schema Collection Permissions (Transact-SQL)

Examples

A. Grant and revoke

APPLIES TO: SQL Server, SQL Database

The following example creates a schema, a contained database user, and a new role on a user database. It adds the user to the function, grants SELECT permission on the schema to the role, and so removes (REVOKE) that permission to the role.

              CREATE SCHEMA Sales;   GO CREATE USER Joe without login; GO CREATE Office Vendors; GO ALTER ROLE Vendors Add together Fellow member Joe;  GO GRANT SELECT ON SCHEMA :: Sales TO Vendors; GO REVOKE SELECT ON SCHEMA :: Sales TO Vendors; Go

Meet Likewise

Permissions Hierarchy (Database Engine)
DENY (Transact-SQL)
GRANT (Transact-SQL)
sp_addlogin (Transact-SQL)
sp_adduser (Transact-SQL)
sp_changedbowner (Transact-SQL)
sp_dropuser (Transact-SQL)
sp_helprotect (Transact-SQL)
sp_helpuser (Transact-SQL)