grant (core)

Syntax

Transact-SQL Syntax

Grant authorization to access database objects:

grant {all [privileges] | permission_list}
 on {table_name [(column_list)]
 | view_name[(column_list)]
 | stored_procedure_name}
 to {public | name_list | role_name}
 [with grant option]

Grant authorization to create database objects:

grant {all [privileges] | command_list}
 to {public | name_list | role_name}

ODBC Syntax

GRANT {ALL|grant_privilege[,grant_privilege]...}

ON table_name

TO {PUBLIC|user_name[,user_name]...}

grant privilege::=
 DELETE
 | INSERT
 | SELECT
 | UPDATE[(column_identifier[,column_identifier]...)]
 | REFERENCES[(column_identifier[,column_identifier]
 ...)]

Parameters

all – when used to assign authorization to access database objects (first syntax format), specifies that all privileges applicable to the specified object are granted or revoked.
public – is all users of the “public” group, which includes all users of the system.
with grant option – allows the users specified in name_list to grant the privileges specified by permission_list to other users.
permission_list – is a list of authorizations granted.
command_list – is a list of commands granted.
table_name – is the name of a table in the database.
column_list – is a list of columns, separated by commas, to which the privileges apply.
view_name – is the name of a view in the current database. Only one view can be listed for each grant command.
stored_procedure – is the name of a stored procedure in the database.
name_list – is a list of user database names or group names or both, separated by commas.
role_name – is the name of an Adaptive Server role. Use it to grant authorizations to all users who have been granted a specific role.

Examples

Example 1 – grants insert and delete permissions on titles table to username Mary.
```
grant insert, delete
 on titles
 to mary, sales
```
Example 2 – grants update permission on titles table to all users.
```
grant update
 on titles (price, advance)
 to public
```
Example 3 – grants create database and create table permission to usernames Mary and John.
```
grant create database, create table
 to mary, john
```
Example 4 – grants update permission on authors table to username Mary.
```
grant update on authors
 to mary
 with grant option
```

Usage

Any valid object in the catalog can be substituted for table_name or view_name.
with grant optionis not available. Transformation removes this phrase.
ODBC does not allow you to grant authorization to a stored procedure.
You can substitute from for to in the grant syntax.
You can grant or revoke authorizations only on objects in the current database.