GRANT (Transact-SQL)

Grants permissions on a securable to a principal.

Syntax

Simplified syntax for GRANT
GRANT { ALL [ PRIVILEGES ] }
      | permission [ ( column [ ,...n ] ) ] [ ,...n ]
      [ ON [ class :: ] securable ] TO principal [ ,...n ] 
      [ WITH GRANT OPTION ] [ AS principal ]

Arguments

• ALL
  This option is deprecated and maintained only for backward compatibility. It does not grant all possible permissions. Granting ALL is equivalent to granting the following permissions.

  • If the securable is a database, "ALL" means BACKUP DATABASE, BACKUP LOG, CREATE DATABASE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, and CREATE VIEW.
  • If the securable is a scalar function, "ALL" means EXECUTE and REFERENCES.
  • If the securable is a table-valued function, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.
  • If the securable is a stored procedure, "ALL" means EXECUTE.
  • If the securable is a table, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.
  • If the securable is a view, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

• PRIVILEGES
  Included for SQL-92 compliance. Does not change the behavior of ALL.

• permission
  The name of a permission. The valid mappings of permissions to securables are described in the sub-topics listed below.

• column
  Specifies the name of a column in a table on which permissions are being granted. The parentheses "()" are required.

• class
  Specifies the class of the securable on which the permission is being granted. The scope qualifier "::" is required.

• securable
  Specifies the securable on which the permission is being granted.

• TO principal
  The name of a principal. The principals to which permissions on a securable can be granted vary, depending on the securable. See the sub-topics listed below for valid combinations.

• GRANT OPTION
  Indicates that the grantee will also be given the ability to grant the specified permission to other principals.

• AS principal
  Specifies a principal from which the principal executing this query derives its right to grant the permission.

Remarks

The full syntax of the GRANT statement is complex. The syntax diagram above has been simplified to draw attention to its structure. Complete syntax for granting permissions on specific securables is described in the topics listed below.

The REVOKE statement can be used to remove granted permissions, and the DENY statement can be used to prevent a principal from gaining a specific permission through a GRANT.

Granting a permission removes DENY or REVOKE of that permission on the specified securable. If the same permission is denied at a higher scope that contains the securable, the DENY takes precedence. But revoking the granted permission at a higher scope does not take precedence.

Database-level permissions are granted within the scope of the specified database. If a user needs permissions to objects in another database, create the user account in the other database, or grant the user account access to the other database, as well as the current database.

Warning

In SQL Server 2005 a table-level DENY does not take precedence over a column-level GRANT. This inconsistency in the permissions hierarchy has been preserved for the sake of backward compatibility. It will be removed in a future release.

The sp_helprotect system stored procedure reports permissions on a database-level securable.

Permissions

The grantor (or the principal specified with the AS option) must have either the permission itself with GRANT OPTION, or a higher permission that implies the permission being granted. If using the AS option, additional requirements apply. See the securable-specific topic for details.

Object owners can grant permissions on the objects they own. Principals with CONTROL permission on a securable can grant permission on that securable.

Grantees of CONTROL SERVER permission, such as members of the sysadmin fixed server role, can grant any permission on any securable in the server. Grantees of CONTROL permission on a database, such as members of the db_owner fixed database role, can grant any permission on any securable in the database. Grantees of CONTROL permission on a schema can grant any permission on any object within the schema.

Examples

See the following topics for securable-specific syntax.

Application Role	GRANT Database Principal Permissions (Transact-SQL)
Assembly	GRANT Assembly Permissions (Transact-SQL)
Asymmetric Key	GRANT Asymmetric Key Permissions (Transact-SQL)
Certificate	GRANT Certificate Permissions (Transact-SQL)
Contract	GRANT Service Broker Permissions (Transact-SQL)
Database	GRANT Database Permissions (Transact-SQL)
Endpoint	GRANT Endpoint Permissions (Transact-SQL)
Full-text Catalog	GRANT Full-text Catalog Permissions (Transact-SQL)
Function	GRANT Object Permissions (Transact-SQL)
Login	GRANT Server Principal Permissions (Transact-SQL)
Message Type	GRANT Service Broker Permissions (Transact-SQL)
Object	GRANT Object Permissions (Transact-SQL)
Queue	GRANT Object Permissions (Transact-SQL)
Remote Service Binding	GRANT Service Broker Permissions (Transact-SQL)
Role	GRANT Database Principal Permissions (Transact-SQL)
Route	GRANT Service Broker Permissions (Transact-SQL)
Schema	GRANT Schema Permissions (Transact-SQL)
Server	GRANT Server Permissions (Transact-SQL)
Service	GRANT Service Broker Permissions (Transact-SQL)
Stored Procedure	GRANT Object Permissions (Transact-SQL)
Symmetric Key	GRANT Symmetric Key Permissions (Transact-SQL)
Synonym	GRANT Object Permissions (Transact-SQL)
System Objects	GRANT System Object Permissions (Transact-SQL)
Table	GRANT Object Permissions (Transact-SQL)
Type	GRANT Type Permissions (Transact-SQL)
User	GRANT Database Principal Permissions (Transact-SQL)
View	GRANT Object Permissions (Transact-SQL)
XML Schema Collection	GRANT XML Schema Collection Permissions (Transact-SQL)

See Also

Reference

DENY (Transact-SQL)
REVOKE (Transact-SQL)
sp_addgroup (Transact-SQL)
sp_addlogin (Transact-SQL)
sp_adduser (Transact-SQL)
sp_changegroup (Transact-SQL)
sp_changedbowner (Transact-SQL)
sp_dropgroup (Transact-SQL)
sp_dropuser (Transact-SQL)
sp_helpgroup (Transact-SQL)
sp_helprotect (Transact-SQL)
sp_helpuser (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
12 December 2006	Changed content: Added information to the description of the ALL option that it is deprecated and maintained only for backward compatibility.