grant (core)

Description

Assigns authorization to users.

Syntax

Transact-SQL Syntax

To 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]

To 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 ASE role. Use it to grant authorizations to all users who have been granted a specific role.

Examples

Example 1

grant insert, delete
 on titles
 to mary, sales

grant update
 on titles (price, advance)
 to public

grant create database, create table
 to mary, john

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 option is 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.