GRANT statement

Grant system and object-level privileges to users and roles.

Syntax

Grant system privileges

GRANT system-privilege [,...]
TO grantee [ ,... ]
[ { WITH NO ADMIN | WITH ADMIN [ ONLY ] } OPTION ]

Grant object-level privileges

GRANT object-level-privilege,...
 ON [ owner.]object-name
 TO to-userid,...
[ WITH GRANT OPTION ]

object-level-privilege : 
ALL [ PRIVILEGES ] 
| ALTER 
| DELETE 
| INSERT 
| LOAD 
| REFERENCES [ ( column-name,... ) ] 
| SELECT [ ( column-name,... ) ] 
| TRUNCATE 
| UPDATE [ ( column-name,... ) ]

Grant the SET USER system privilege

GRANT SET USER [ ( user-list | ANY [ WITH ROLES role-list ] ) ]
TO grantee [,...]
[ { WITH NO ADMIN | WITH ADMIN [ ONLY ] } OPTION ]

Grant the CHANGE PASSWORD system privilege

GRANT CHANGE PASSWORD [ ( user-list | ANY [ WITH ROLES role-list ] ) ]
TO grantee [,...]
[ { WITH NO ADMIN | WITH ADMIN [ ONLY ] } OPTION ]

Parameters

grantee
The user ID of a user, or the name of a role. You cannot grant privileges to compatibility roles. You can grant privileges to any system role; however, only the following system roles support logins: PUBLIC, dbo, diagnostics, rs_systabgroup, and SA_DEBUG
object-level-privilege
- ALL privilege
  This privilege grants ALTER, DELETE, INSERT, REFERENCES, SELECT, and UPDATE privileges on tables. This privilege grants DELETE, INSERT, and UPDATE privileges on views.
- ALTER privilege
  This privilege allows the user to alter the named table with the ALTER TABLE statement. This privilege is not allowed for views.
- DELETE privilege
  This privilege allows the user to delete rows from the named table or view.
- INSERT privilege
  This privilege allows the user to insert rows into the named table or view.
- LOAD privilege
  This privilege allows the user to load the named table or view.
- REFERENCES privilege
  This privilege allows the user to create indexes on the named table and on the foreign keys that reference the named tables. If column names are specified, the user can reference only those columns. REFERENCES privileges on columns cannot be granted for views, only for tables. INDEX is a synonym for REFERENCES.
- SELECT privilege
  This privilege allows the user to view information in the view or table. If column names are specified, the users are allowed to view only those columns. SELECT privileges on columns cannot be granted for views, only for tables.
- TRUNCATE privilege
  This privilege allows the user to truncate the named object.
- UPDATE privilege
  This privilege allows the user to update rows in the view or table. If column names are specified, the user can update only those columns.
- WITH GRANT OPTION
  If WITH GRANT OPTION is specified, then the named user ID is also given permission to GRANT the same privilege to other user IDs. Users who can exercise a role do not inherit the WITH GRANT OPTION if it is granted to a role.
FROM internal-id
This clause is for internal use only.
GRANT SET USER
- user-list
  Specify the comma-separated list of all user IDs (targets) users that grantee-list can impersonate. For example: GRANT SET USER(u1, u2, u3)...
- ANY [ WITH ROLES target_role_list ] clause
  Specify who grantee can impersonate without providing specific user IDs.
  
  If just ANY is specified, then the user can impersonate any other user. This is the default.
  
  If ANY WITH ROLES role-list is specified, users in grantee-list can impersonate anyone who has at least one of the roles listed in role-list, where role-list is a comma-separated list of roles.
- WITH ADMIN [ ONLY ] OPTION option
  The WITH ADMIN OPTION and WITH ADMIN ONLY OPTION clauses can only be specified with the ANY clause.
GRANT CHANGE PASSWORD
- user-list
  Specify a comma-separated list of users that grantee can change passwords for.
- ANY [ WITH ROLES role-list ]
  Specify who grantee can change the password for without providing specific user IDs.
  
  If just ANY is specified, then the user can change any user's password. This is the default.
  
  If ANY WITH ROLES role-list is specified, the grantee can change the password for anyone who has at least one of the roles listed in role-list, where role-list is a comma-separated list of roles.
- WITH ADMIN [ ONLY ] OPTION option
  The WITH ADMIN OPTION and WITH ADMIN ONLY OPTION clauses can only be specified with the ANY clause.

Remarks

You can grant privileges on disabled objects. Privileges on disabled objects are stored in the database and become effective when the object is enabled.

With the exception of the SYS role, you can grant/revoke additional privileges to/from a system role, provided you have administrative rights on the privileges you are granting/revoking.

Granting SET USER to a user multiple times, specifying different user IDs they can impersonate, grants additional users to the list they can impersonate (as opposed to overwriting the previous grants).

Granting impersonation rights (GRANT SET USER) is not an indication of whether a user can successfully impersonate another user. Evaluation of whether a user can impersonate another user is done when the user ID attempts to start impersonating another user by executing a SETUSER statement. The impersonating user must have the SET USER system privilege, and must meet the at-least criteria required for impersonation.

The GRANT syntax related to authorities, permissions, and groups used in pre-16.0 versions of the software is still supported but deprecated.

When granting system privileges to the MANAGE ROLES system privilege, you must use the special internal representation for MANAGE ROLES, which is SYS_MANAGE_ROLES_ROLE (for example, GRANT privilege-name TO SYS_MANAGE_ROLES_ROLE;)

Privileges

You must have administrative rights for each privilege you grant.

To grant object-level privileges, you must also have the MANAGE ANY OBJECT PRIVILEGE system privilege, with administrative rights.

Side effects

None

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

Granting system privileges to roles
To grant the CREATE ANY OBJECT system privilege to the role RoleA without giving RoleA administrative rights, execute the following statement:
```
GRANT CREATE ANY OBJECT TO RoleA;
```
To grant RoleA the CREATE ANY OBJECT system privilege along with the ability to grant or revoke the system privilege to and from other users and roles, execute the following statement:
```
GRANT CREATE ANY OBJECT TO RoleA WITH ADMIN OPTION;
```
To give RoleA administrative rights to the BACKUP DATABASE system privilege, but not the ability to use the BACKUP DATABASE privilege, execute the following statement:
```
GRANT BACKUP DATABASE TO RoleA WITH ADMIN ONLY OPTION;
```
Granting SET USER to users
The following example specifies that User4 and User5 can impersonate User1, User2, and User3.
```
GRANT SET USER ( User1, User2, User3 ) TO User4, User5;
```
The following example specifies that User1 can impersonate any user in the database. As well, User1 can grant the SET USER system privilege to other users.
```
GRANT SET USER (ANY) TO User1 WITH ADMIN OPTION;
```
The following example specifies that User1 can impersonate any user who has been granted the SYS_AUTH_BACKUP_ROLE compatibility role.
```
GRANT SET USER (ANY WITH ROLES SYS_AUTH_BACKUP_ROLE) TO User1;
```