GRANT Statement

For SQL Server 6.5 information, see GRANT Statement in What's New for SQL Server 6.5.

Assigns permissions to users.

Syntax

    Statement permissions:

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

    Object permissions:

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

where

ALL
When used to assign object permissions, specifies that all permissions applicable to the specified object are granted or revoked. When used to assign statement permissions, only the system administrator can use ALL, since only the system administrator can grant or revoke CREATE DATABASE permission.
statement_list
Lists statements granted. The statement list can include CREATE DATABASE (if the user executing the statement is the system administrator), CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW, DUMP DATABASE, and DUMP TRANSACTION.

If more than one statement is listed, separate them with commas.

PUBLIC
Specifies all users, including those who are a member of another group in addition to public.
name_list
Lists users' database names and/or group names, separated by commas. These names are stored in the database's sysusers table.
permission_list
Lists permissions granted. When permissions are granted on a table or a view, the permission list can include one or more of the following items: SELECT, INSERT, DELETE, and UPDATE.

Permissions granted on a table can also include REFERENCES. REFERENCES allows a user to create FOREIGN KEY constraints that reference another table without having SELECT permission on that table. REFERENCES permission needs to be granted only to the person creating the FOREIGN KEY.

When permissions are granted on columns, the permission list can include SELECT and/or UPDATE.

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

If multiple permissions are listed, separate them with commas.

table_name
Specifies a table in the current database. Only one table can be listed for each GRANT statement.
column_list
Lists names of columns, separated by commas, to which the permissions apply. If columns are specified, only SELECT and UPDATE permissions can be granted.
view_name
Specifies 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 in the current database. Only one stored procedure can be listed for each GRANT statement.
extended_stored_procedure_name
Is the name of an extended stored procedure in the current database. Only one extended stored procedure can be listed for each GRANT statement.

Remarks

Permission to use a statement or object defaults to the system administrator, the database owner, the object owner, or the default group public. Some permissions are transferable. When possible, the user to whom the permissions default can transfer permission to other users. Users at higher levels (the database owner and system administrator) are either automatically granted the permission or (in the case of database owners) can temporarily obtain permissions.

By default, the system administrator has all permissions. The system administrator can assign a database owner by using the sp_changedbowner system stored procedure. Once assigned, a database owner (or the system administrator) can GRANT "statement" permissions to users within that database. Once a user has been given "statement" permissions, he or she can create an object. That user becomes the object owner of the object(s) he or she has created. Object owners always retain rights to their object(s). "Object" permissions must be granted to other users before they can access that object.

Database users can belong to only one group in addition to the group public. Users are always a member of the group public. For permissions that default to public, no permission is required ¾ that is, no explicit GRANT or REVOKE statement must be executed.

When permissions are given, information is stored within the sysprotects table. The sysprotects table manages permissions by updating rows based on each GRANT and REVOKE statement. You can grant or revoke permissions only on objects in the current database.

If permissions are granted to the group public, they will override any previous permissions given to any other user or group of that database. When permissions are given (or revoked) the permissions are order-sensitive. (The statement executed most recently is the one that takes effect.) This can produce undesired results if care is not taken in granting/revoking permissions. For best results, grant/revoke permissions to the least-selective group first (usually public), then grant/revoke permissions on a group-by-group basis, on an individual-by-individual basis, and then to/from the guest user.

A guest user can be added by using the sp_adduser system stored procedure to allow users with login IDs on SQL Server to use the database without a specific database username (and usually with limited permissions).

The sp_helprotect system stored procedure reports permissions on a database object or on a user. For details, see the sp_helprotect system stored procedure.

Permission

GRANT and REVOKE permission defaults to particular users and cannot be transferred.

The following table details permissions for Transact-SQL statements:


Defaults to		 Can be
granted /revoked

Statement		 System admin DB
owner		 Object
owner
Public
Yes
No
N/A
ALTER DATABASE ¾ X ¾ ¾ (1) ¾ ¾
ALTER TABLE ¾ ¾ X ¾ ¾ X ¾
BEGIN TRANSACTION ¾ ¾ ¾ X ¾ ¾ X
CHECKPOINT ¾ X ¾ ¾ ¾ X ¾
COMMIT TRANSACTION ¾ ¾ ¾ X ¾ ¾ X
CREATE DATABASE X ¾ ¾ ¾ X ¾ ¾
CREATE DEFAULT ¾ X ¾ ¾ X ¾ ¾
CREATE INDEX ¾ ¾ X (3) ¾ ¾ X ¾
CREATE PROCEDURE ¾ X ¾ ¾ X ¾ ¾
CREATE RULE ¾ X ¾ ¾ X ¾ ¾
CREATE TABLE ¾ X ¾ (2) X (2) ¾ ¾
CREATE TRIGGER ¾ ¾ X (3) ¾ ¾ X ¾
CREATE VIEW ¾ X ¾ ¾ X ¾ ¾
DBCC (4) ¾ X ¾ ¾ ¾ X ¾
DELETE ¾ ¾ X ¾ X ¾ ¾
DISK INIT X ¾ ¾ ¾ ¾ X ¾
DISK MIRROR X ¾ ¾ ¾ ¾ X ¾
DISK REFIT X ¾ ¾ ¾ ¾ X ¾
DISK REINIT X ¾ ¾ ¾ ¾ X ¾
DISK REMIRROR X ¾ ¾ ¾ ¾ X ¾
DISK UNMIRROR X ¾ ¾ ¾ ¾ X ¾
DROP any object ¾ ¾ X ¾ ¾ X ¾
DUMP DATABASE ¾ X ¾ ¾ X ¾ ¾
DUMP TRANSACTION ¾ X ¾ ¾ X ¾ ¾
EXECUTE (5) ¾ ¾ X ¾ X ¾ ¾
GRANT ¾ X ¾ ¾ ¾ X ¾
GRANT on object ¾ ¾ X ¾ ¾ X ¾
INSERT ¾ ¾ X ¾ X ¾ ¾
KILL X ¾ ¾ ¾ ¾ X ¾
LOAD DATABASE ¾ X ¾ ¾ ¾ X ¾
LOAD TRANSACTION ¾ X ¾ ¾ ¾ X ¾
PRINT ¾ ¾ ¾ X ¾ ¾ X
RAISERROR ¾ ¾ ¾ X ¾ ¾ X
READTEXT ¾ ¾ X ¾ (6) ¾ ¾
RECONFIGURE X ¾ ¾ ¾ ¾ X ¾
REFERENCES ¾ ¾ X ¾ X ¾ ¾
REVOKE ¾ X ¾ ¾ ¾ X ¾
REVOKE on object ¾ ¾ X ¾ ¾ X ¾
ROLLBACK TRANSACTION ¾ ¾ ¾ X ¾ ¾ X
SAVE TRANSACTION ¾ ¾ ¾ X ¾ ¾ X
SELECT ¾ ¾ X ¾ X ¾ ¾
SET ¾ ¾ ¾ X ¾ ¾ X
SETUSER ¾ X ¾ ¾ ¾ X ¾
SHUTDOWN X ¾ ¾ ¾ ¾ X ¾
TRUNCATE TABLE ¾ ¾ X ¾ ¾ X ¾
UPDATE ¾ ¾ X ¾ X ¾ ¾
UPDATE STATISTICS ¾ ¾ X ¾ ¾ X ¾
UPDATETEXT ¾ ¾ X ¾ (7) ¾ ¾
WRITETEXT ¾ ¾ X ¾ (7) ¾ ¾
(1) Transferred with CREATE DATABASE permission.
(2) Public can create temporary tables; no permission required.
(3) Defaults to the table owner.
(4) For the list of all DBCC statement permissions see the DBCC statement.
(5) Defaults to stored-procedure owner.
(6) Transferred with SELECT permission.
(7) Transferred with UPDATE permission.

Examples

A.    Grant "Statement" Permissions

This example shows how to grant multiple statement permissions to multiple users.

GRANT CREATE DATABASE, CREATE TABLE
TO Mary, John
B.    Grant "Object" Permissions/Permission Hierarchy

This example shows preferred ordering of permissions. First, SELECT permissions are given to the group public. After this, specific permissions are given to a few users. These users (Mary, John, and Tom) then have all permissions to the authors table.

GRANT SELECT
ON authors
TO public
go 
GRANT INSERT, UPDATE, DELETE
ON authors
TO Mary, John, Tom

See Also

REVOKE sp_changegroup
SETUSER sp_dropgroup
sp_addgroup sp_dropuser
sp_addlogin sp_helpgroup
sp_adduser sp_helprotect
sp_changedbowner sp_helpuser