GRANT Statement (version 6.5)

Provides ANSI-compliant syntax for column-level grants and the ability to confer grant permissions to others.

For additional syntax information for the GRANT statement, see the Microsoft SQL Server Transact-SQL Reference.

Syntax

To grant permission to access database objects:

GRANT {ALL [PRIVILEGES][column_list] | permission_list [column_list]}
    ON {table_name [(column_list)]
     | view_name [(column_list)]
     | stored_procedure_name}
    TO {PUBLIC | name_list }
    [WITH GRANT OPTION]

To grant permission to execute specific SQL statements in the current database:

GRANT {ALL | statement_list}
    TO { PUBLIC | name_list}

where

ALL

When used to grant permission to access database objects, specifies that all permissions possessed by the current user (grantor) will be granted to the grantee. When used to assign statement permissions, only the system administrator can use ALL, because only the system administrator can grant or revoke CREATE DATABASE permission.

permission_list

Is the list of permissions granted. When permissions are granted on a column, the permission list can include only SELECT, REFERENCES, and/or UPDATE.

When permissions are granted on a table or view, the permission list can include: SELECT, INSERT, DELETE, and/or UPDATE. Permissions on a table can also include REFERENCES. To grant REFERENCES permission, you must specify REFERENCES because it is not automatically included in the SELECT permission.

When permissions are granted on stored procedures, the permission list can include only EXECUTE.

table_name

Is a table in the current database. Only one table can be listed for each GRANT statement.

column_list

Is a list of columns names delimited by commas, to which the permissions apply. If columns are specified, only SELECT and UPDATE permissions can be granted.

view_name

Is a view in the current database. Only one view can be listed for each GRANT statement.

stored_procedure_name

Is the name of a stored procedure or extended stored procedure in the current database. Only one stored procedure can be listed for each GRANT statement.

PUBLIC

Is all users, including those who are members of a user-defined group.

name_list

Is a list of user names and/or group names delimited by commas. These names are stored in the sysusers table of the database.

WITH GRANT OPTION

Extends the ability to grant the specified permission or statement to the grantee.

Note Users can neither grant the WITH GRANT OPTION permission to a group nor use it with statement permissions.

statement_list

Is a list of statements granted. The statement list can include: CREATE DATABASE, CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW, DUMP DATABASE, and DUMP TRANSACTION.

Remarks

You can confer, along with a permission, the ability to confer that permission to another user. For example, suppose the owner of an object grants Bob INSERT permission and trusts Bob to determine who else in his work group should get INSERT permission. By using WITH GRANT OPTION, the owner of the object gives Bob permission to grant INSERT permission to others.

Granting a permission to a user who has already received the WITH GRANT OPTION permission through a group or public grant will be recorded in the sysprotects table. If the permission is revoked for the group, the user will still retain the permission.

Important The WITH GRANT OPTION should be managed carefully. Security can be compromised when the option is used improperly.

Users may need SELECT permission in addition to UPDATE permission to update a column or view. SELECT permissions are required only when there are column names after SET or WHERE in the UPDATE statement.

For more information about the GRANT statement, see the Microsoft SQL Server Transact-SQL Reference.

For more information about the sysprotects table, see sysprotects System Table.

Examples

A.    Use New ANSI Syntax for Column-level GRANT

This example grants SELECT permissions on population_table and UPDATE permissions on the census_org column of the population_table to the admin_group.

Users in the admin_group can view the entire population_table and can change the values in the census_org column.

GRANT SELECT, UPDATE(census_org) ON population_table TO admin_group
  

B.    Use WITH GRANT OPTION

In this example, the object owner grants UPDATE permission to user zachary on the city_statistics table.

User zachary then gives SELECT permission to user matthew. Note that because user matthew does not have the WITH GRANT OPTION, he cannot confer this permission to anyone else.

GRANT SELECT ON city_statistics TO zachary WITH GRANT OPTION
GRANT ALL ON city_statistics TO matthew