GRANT Statement (Transact-SQL)

Syntax

GRANT permission
    [ ,...n ] TO principal
    [ WITH GRANT OPTION ]

Description

The GRANT statement grants a permission or a list of permissions on a securable to a security principal (a user, role, or application role). Permissions define what actions a principal can perform on a securable.

GRANT is a fundamental Data Control Language (DCL) statement used to manage access to database objects. It allows administrators to specify which users or roles have specific privileges to perform operations like selecting data, inserting data, executing stored procedures, or altering table structures.

Note: In Azure SQL Database, GRANT statements are used to manage permissions. For SQL Server on virtual machines or on-premises, DCL statements are managed within the SQL Server instance itself.

Parameters

Parameter Description
permission The permission being granted. This can be a specific permission (e.g., SELECT, INSERT, EXECUTE) or a permission set (e.g., CONTROL, UPDATE, REFERENCES). The available permissions depend on the securable.
principal The security principal (user, role, or application role) to which the permission is being granted.
WITH GRANT OPTION

Optional. Allows the principal to whom the permission is granted to grant that same permission to other principals.

Permissions

The type of permissions that can be granted varies depending on the securable object. Common permissions include:

  • Data Permissions: SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES
  • Object Permissions: ALTER, CONTROL, CREATE, DELETE, DROP, EXECUTE, INSERT, SELECT, UPDATE
  • Schema Permissions: ALTER, CONTROL, CREATE, DELETE, DROP, EXECUTE, INSERT, SELECT, UPDATE
  • Database Permissions: ALTER ANY DATABASE, CONNECT, CONTROL DATABASE, CREATE TABLE, VIEW DEFINITION

For a comprehensive list of permissions by securable, refer to the Permissions documentation.

Examples

Granting SELECT permission on a table

GRANT SELECT ON dbo.Customers TO WebAppUser;

Granting INSERT and UPDATE permissions with GRANT OPTION

GRANT INSERT, UPDATE
    ON dbo.Orders
    TO OrderManager
    WITH GRANT OPTION;

Granting EXECUTE permission on a stored procedure

GRANT EXECUTE ON dbo.usp_ProcessPayment TO PaymentProcessorRole;

Granting CONTROL permission on a database

GRANT CONTROL ON DATABASE::AdventureWorks2019 TO db_owner_role;

See Also