REVOKE ROLE Statement

Syntax

REVOKE [ADMIN OPTION FOR] ROLE role_name [,...] 
  FROM grantee [,...]

role_name:
dbo^†††
| diagnostics^†††
| PUBLIC^†††
| rs_systabgroup^†††
| SA_DEBUG^†††
| SYS^†††
| SYS_AUTH_SA_ROLE
| SYS_AUTH_SSO_ROLE
| SYS_AUTH_DBA_ROLE 
| SYS_AUTH_RESOURCE_ROLE 
| SYS_AUTH_BACKUP_ROLE
| SYS_AUTH_VALIDATE_ROLE 
| SYS_AUTH_WRITEFILE_ROLE 
| SYS_AUTH_WRITEFILECLIENT_ROLE
| SYS_AUTH_READFILE_ROLE 
| SYS_AUTH_READFILECLIENT_ROLE 
| SYS_AUTH_PROFILE_ROLE 
| SYS_AUTH_USER_ADMIN_ROLE 
| SYS_AUTH_SPACE_ADMIN_ROLE 
| SYS_AUTH_MULTIPLEX_ADMIN_ROLE 
| SYS_AUTH_OPERATOR_ROLE 
| SYS_AUTH_PERMS_ADMIN_ROLE
| SYS_REPLICATE_ADMIN_ROLE^†††
| SYS_RUN_REPLICATE_ROLE^†††
| SYS_SPATIAL_ADMIN_ROLE^†††
| <user-defined role name>

^†††The ADMIN OPTION FOR clause is not supported for system roles.

Parameters

role_name – must already exist in the database. Separate multiple role names with commas..
userID – must be the name of an existing user or role that has a login password. Separate multiple userIDs with commas.
ADMIN OPTION FOR – each userID must have been granted administrative privilege over the specified role_name.
Note: This clause revokes administrative privileges of the role only, not membership in the role, unless the role was originally granted with the WITH ADMIN ONLY OPTION clause. For roles granted with the WITH ADMIN ONLY OPTION clause, the ADMIN OPTION FOR clause is optional as it is semantically equivalent to revoking membership in a role in its entirety.

Examples

Example 1 – revokes the user-defined (standalone) role Role1 from User1.
```
REVOKE ROLE Role1 FROM User1
```
After you execute this command, User1 no longer has the rights to perform any authorized tasks using any system privileges granted to Role1.
Example 2 – revokes the ability for User1 to administer the compatibility role SYS_AUTH_WRITEFILE_ROLE.
```
REVOKE ADMIN OPTION FOR ROLE SYS_AUTH_WRITEFILE_ROLE FROM User1
```
User1 retains the ability to perform any authorized tasks granted by SYS_AUTH_WRITEFILE_ROLE.

Standards

SQL – Other syntaxes are vendor extensions to ISO/ANSI SQL grammar.
Sybase – Syntax is supported in Adaptive Server Enterprise.

Permissions

Requires the MANAGE ROLES system privilege to revoke these roles:

diagnostics
dbo
PUBLIC
rs_systabgroup
SA_DEBUG
SYS
SYS_RUN_REPLICATE_ROLE
SYS_SPATIAL_ADMIN_ROLE

Requires administrative privilege over the role to revoke these roles:

SYS_AUTH_SA_ROLE
SYS_AUTH_SSO_ROLE
SYS_AUTH_DBA_ROLE
SYS_AUTH_RESOURCE_ROLE
SYS_AUTH_BACKUP_ROLE
SYS_AUTH_VALIDATE_ROLE
SYS_AUTH_WRITEFILE_ROLE
SYS_AUTH_WRITEFILECLIENT_ROLE
SYS_AUTH_READFILE_ROLE
SYS_AUTH_READFILECLIENT_ROLE
SYS_AUTH_PROFILE_ROLE
SYS_AUTH_USER_ADMIN_ROLE
SYS_AUTH_SPACE_ADMIN_ROLE
SYS_AUTH_MULTIPLEX_ADMIN_ROLE
SYS_AUTH_OPERATOR_ROLE
SYS_AUTH_PERMS_ADMIN_ROLE
<user-defined role name>