GRANT Database Permissions (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
SQL database in Microsoft Fabric
Grants permissions on a database in SQL Server.
Transact-SQL syntax conventions
Syntax
GRANT <permission> [ ,...n ]
TO <database_principal> [ ,...n ] [ WITH GRANT OPTION ]
[ AS <database_principal> ]
<permission>::=
permission | ALL [ PRIVILEGES ]
<database_principal> ::=
Database_user
| Database_role
| Application_role
| Database_user_mapped_to_Windows_User
| Database_user_mapped_to_Windows_Group
| Database_user_mapped_to_certificate
| Database_user_mapped_to_asymmetric_key
| Database_user_with_no_login
Arguments
permission Specifies a permission that can be granted on a database. For a list of the permissions, see the Remarks section later in this topic.
ALL This option does not grant all possible permissions. Granting ALL is equivalent to granting the following permissions: BACKUP DATABASE, BACKUP LOG, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, and CREATE VIEW.
PRIVILEGES Included for ANSI-92 compliance. Does not change the behavior of ALL.
WITH GRANT OPTION Indicates that the principal will also be given the ability to grant the specified permission to other principals.
AS <database_principal> Specifies a principal from which the principal executing this query derives its right to grant the permission.
Database_user Specifies a database user.
Database_role Specifies a database role.
Application_role Applies to: SQL Server 2008 (10.0.x) and later, SQL Database
Specifies an application role.
Database_user_mapped_to_Windows_User Applies to: SQL Server 2008 (10.0.x) and later
Specifies a database user mapped to a Windows user.
Database_user_mapped_to_Windows_Group Applies to: SQL Server 2008 (10.0.x) and later
Specifies a database user mapped to a Windows group.
Database_user_mapped_to_certificate Applies to: SQL Server 2008 (10.0.x) and later
Specifies a database user mapped to a certificate.
Database_user_mapped_to_asymmetric_key Applies to: SQL Server 2008 (10.0.x) and later
Specifies a database user mapped to an asymmetric key.
Database_user_with_no_login Specifies a database user with no corresponding server-level principal.
Remarks
Important
A combination of ALTER and REFERENCE permissions in some cases could allow the grantee to view data or execute unauthorized functions. For example: A user with ALTER permission on a table and REFERENCE permission on a function can create a computed column over a function and have it be executed. In this case, the user must also have SELECT permission on the computed column.
A database is a securable contained by the server that is its parent in the permissions hierarchy. The most specific and limited permissions that can be granted on a database are listed in the following table, together with the more general permissions that include them by implication.
| Database permission | Implied by database permission | Implied by server permission |
|---|---|---|
| ADMINISTER DATABASE BULK OPERATIONS Applies to: SQL Database. |
CONTROL | CONTROL SERVER |
| ALTER | CONTROL | ALTER ANY DATABASE |
| ALTER ANY APPLICATION ROLE | ALTER | CONTROL SERVER |
| ALTER ANY ASSEMBLY | ALTER | CONTROL SERVER |
| ALTER ANY ASYMMETRIC KEY | ALTER | CONTROL SERVER |
| ALTER ANY CERTIFICATE | ALTER | CONTROL SERVER |
| ALTER ANY COLUMN ENCRYPTION KEY | ALTER | CONTROL SERVER |
| ALTER ANY COLUMN MASTER KEY DEFINITION | ALTER | CONTROL SERVER |
| ALTER ANY CONTRACT | ALTER | CONTROL SERVER |
| ALTER ANY DATABASE AUDIT | ALTER | ALTER ANY SERVER AUDIT |
| ALTER ANY DATABASE DDL TRIGGER | ALTER | CONTROL SERVER |
| ALTER ANY DATABASE EVENT NOTIFICATION | ALTER | ALTER ANY EVENT NOTIFICATION |
| ALTER ANY DATABASE EVENT SESSION Applies to: SQL Database. |
ALTER | ALTER ANY EVENT SESSION |
| ALTER ANY DATABASE SCOPED CONFIGURATION Applies to: SQL Server 2016 (13.x) and later, SQL Database. |
CONTROL | CONTROL SERVER |
| ALTER ANY DATASPACE | ALTER | CONTROL SERVER |
| ALTER ANY EXTERNAL DATA SOURCE | ALTER | CONTROL SERVER |
| ALTER ANY EXTERNAL FILE FORMAT | ALTER | CONTROL SERVER |
| ALTER ANY EXTERNAL LIBRARY Applies to: SQL Server 2017 (14.x). |
CONTROL | CONTROL SERVER |
| ALTER ANY FULLTEXT CATALOG | ALTER | CONTROL SERVER |
| ALTER ANY MASK | CONTROL | CONTROL SERVER |
| ALTER ANY MESSAGE TYPE | ALTER | CONTROL SERVER |
| ALTER ANY REMOTE SERVICE BINDING | ALTER | CONTROL SERVER |
| ALTER ANY ROLE | ALTER | CONTROL SERVER |
| ALTER ANY ROUTE | ALTER | CONTROL SERVER |
| ALTER ANY SCHEMA | ALTER | CONTROL SERVER |
| ALTER ANY SECURITY POLICY Applies to: Azure SQL Database. |
CONTROL | CONTROL SERVER |
| ALTER ANY SENSITIVITY CLASSIFICATION Applies to: SQL Server (SQL Server 2019 and later), Azure SQL Database. |
CONTROL | CONTROL SERVER |
| ALTER ANY SERVICE | ALTER | CONTROL SERVER |
| ALTER ANY SYMMETRIC KEY | ALTER | CONTROL SERVER |
| ALTER ANY USER | ALTER | CONTROL SERVER |
| AUTHENTICATE | CONTROL | AUTHENTICATE SERVER |
| BACKUP DATABASE | CONTROL | CONTROL SERVER |
| BACKUP LOG | CONTROL | CONTROL SERVER |
| CHECKPOINT | CONTROL | CONTROL SERVER |
| CONNECT | CONNECT REPLICATION | CONTROL SERVER |
| CONNECT REPLICATION | CONTROL | CONTROL SERVER |
| CONTROL | CONTROL | CONTROL SERVER |
| CREATE AGGREGATE | ALTER | CONTROL SERVER |
| CREATE ANY EXTERNAL LIBRARY Applies to: SQL Server 2017 (14.x). |
CONTROL | CONTROL SERVER |
| CREATE ASSEMBLY | ALTER ANY ASSEMBLY | CONTROL SERVER |
| CREATE ASYMMETRIC KEY | ALTER ANY ASYMMETRIC KEY | CONTROL SERVER |
| CREATE CERTIFICATE | ALTER ANY CERTIFICATE | CONTROL SERVER |
| CREATE CONTRACT | ALTER ANY CONTRACT | CONTROL SERVER |
| CREATE DATABASE | CONTROL | CREATE ANY DATABASE |
| CREATE DATABASE DDL EVENT NOTIFICATION | ALTER ANY DATABASE EVENT NOTIFICATION | CREATE DDL EVENT NOTIFICATION |
| CREATE DEFAULT | ALTER | CONTROL SERVER |
| CREATE FULLTEXT CATALOG | ALTER ANY FULLTEXT CATALOG | CONTROL SERVER |
| CREATE FUNCTION | ALTER | CONTROL SERVER |
| CREATE MESSAGE TYPE | ALTER ANY MESSAGE TYPE | CONTROL SERVER |
| CREATE PROCEDURE | ALTER | CONTROL SERVER |
| CREATE QUEUE | ALTER | CONTROL SERVER |
| CREATE REMOTE SERVICE BINDING | ALTER ANY REMOTE SERVICE BINDING | CONTROL SERVER |
| CREATE ROLE | ALTER ANY ROLE | CONTROL SERVER |
| CREATE ROUTE | ALTER ANY ROUTE | CONTROL SERVER |
| CREATE RULE | ALTER | CONTROL SERVER |
| CREATE SCHEMA | ALTER ANY SCHEMA | CONTROL SERVER |
| CREATE SERVICE | ALTER ANY SERVICE | CONTROL SERVER |
| CREATE SYMMETRIC KEY | ALTER ANY SYMMETRIC KEY | CONTROL SERVER |
| CREATE SYNONYM | ALTER | CONTROL SERVER |
| CREATE TABLE | ALTER | CONTROL SERVER |
| CREATE TYPE | ALTER | CONTROL SERVER |
| CREATE VIEW | ALTER | CONTROL SERVER |
| CREATE XML SCHEMA COLLECTION | ALTER | CONTROL SERVER |
| DELETE | CONTROL | CONTROL SERVER |
| EXECUTE | CONTROL | CONTROL SERVER |
| EXECUTE ANY EXTERNAL ENDPOINT Applies to: Azure SQL Database. |
CONTROL | CONTROL SERVER |
| EXECUTE ANY EXTERNAL SCRIPT Applies to: SQL Server 2016 (13.x). |
CONTROL | CONTROL SERVER |
| EXECUTE EXTERNAL SCRIPT Applies to: SQL Server 2019 (15.x). |
EXECUTE ANY EXTERNAL SCRIPT | CONTROL SERVER |
| INSERT | CONTROL | CONTROL SERVER |
| KILL DATABASE CONNECTION Applies to: Azure SQL Database. |
CONTROL | ALTER ANY CONNECTION |
| REFERENCES | CONTROL | CONTROL SERVER |
| SELECT | CONTROL | CONTROL SERVER |
| SHOWPLAN | CONTROL | ALTER TRACE |
| SUBSCRIBE QUERY NOTIFICATIONS | CONTROL | CONTROL SERVER |
| TAKE OWNERSHIP | CONTROL | CONTROL SERVER |
| UNMASK | CONTROL | CONTROL SERVER |
| UPDATE | CONTROL | CONTROL SERVER |
| VIEW ANY COLUMN ENCRYPTION KEY DEFINITION | CONTROL | VIEW ANY DEFINITION |
| VIEW ANY COLUMN MASTER KEY DEFINITION | CONTROL | VIEW ANY DEFINITION |
| VIEW DATABASE STATE | CONTROL | VIEW SERVER STATE |
| VIEW DEFINITION | CONTROL | VIEW ANY DEFINITION |
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 you are using the AS option, the following additional requirements apply.
| AS granting_principal | Additional permission required |
|---|---|
| Database user | IMPERSONATE permission on the user, membership in the db_securityadmin fixed database role, membership in the db_owner fixed database role, or membership in the sysadmin fixed server role. |
| Database user mapped to a Windows login | IMPERSONATE permission on the user, membership in the db_securityadmin fixed database role, membership in the db_owner fixed database role, or membership in the sysadmin fixed server role. |
| Database user mapped to a Windows Group | Membership in the Windows group, membership in the db_securityadmin fixed database role, membership in the db_owner fixed database role, or membership in the sysadmin fixed server role. |
| Database user mapped to a certificate | Membership in the db_securityadmin fixed database role, membership in the db_owner fixed database role, or membership in the sysadmin fixed server role. |
| Database user mapped to an asymmetric key | Membership in the db_securityadmin fixed database role, membership in the db_owner fixed database role, or membership in the sysadmin fixed server role. |
| Database user not mapped to any server principal | IMPERSONATE permission on the user, membership in the db_securityadmin fixed database role, membership in the db_owner fixed database role, or membership in the sysadmin fixed server role. |
| Database role | ALTER permission on the role, membership in the db_securityadmin fixed database role, membership in the db_owner fixed database role, or membership in the sysadmin fixed server role. |
| Application role | ALTER permission on the role, membership in the db_securityadmin fixed database role, membership in the db_owner fixed database role, or membership in the sysadmin fixed server role. |
Object owners can grant permissions on the objects they own. Principals that have 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.
Examples
A. Granting permission to create tables
The following example grants CREATE TABLE permission on the AdventureWorks database to user MelanieK.
USE AdventureWorks;
GRANT CREATE TABLE TO MelanieK;
GO
B. Granting SHOWPLAN permission to an application role
The following example grants SHOWPLAN permission on the AdventureWorks2022 database to application role AuditMonitor.
Applies to: SQL Server 2008 (10.0.x) and later, SQL Database
USE AdventureWorks2022;
GRANT SHOWPLAN TO AuditMonitor;
GO
C. Granting CREATE VIEW with GRANT OPTION
The following example grants CREATE VIEW permission on the AdventureWorks2022 database to user CarmineEs with the right to grant CREATE VIEW to other principals.
USE AdventureWorks2022;
GRANT CREATE VIEW TO CarmineEs WITH GRANT OPTION;
GO
D. Granting CONTROL permission to a database user
The following example grants CONTROL permission on the AdventureWorks2022 database to the database user Sarah. The user must exist in the database and the context must be set to the database.
USE AdventureWorks2022;
GRANT CONTROL ON DATABASE::AdventureWorks2022 TO Sarah;
GO