GRANT Database Permissions (Transact-SQL)

GRANT Database Permissions (Transact-SQL)

 

Updated: March 11, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Grants permissions on a database in SQL Server.

Topic link icon Transact-SQL Syntax Conventions

  
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    

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 DATABASE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, and CREATE VIEW.

PRIVILEGES
Included for ISO 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 through SQL Server 2016, SQL Database V12.

Specifies an application role.

Database_user_mapped_to_Windows_User

Applies to: SQL Server 2008 through SQL Server 2016.

Specifies a database user mapped to a Windows user.

Database_user_mapped_to_Windows_Group

Applies to: SQL Server 2008 through SQL Server 2016.

Specifies a database user mapped to a Windows group.

Database_user_mapped_to_certificate

Applies to: SQL Server 2008 through SQL Server 2016.

Specifies a database user mapped to a certificate.

Database_user_mapped_to_asymmetric_key

Applies to: SQL Server 2008 through SQL Server 2016.

Specifies a database user mapped to an asymmetric key.

Database_user_with_no_login
Specifies a database user with no corresponding server-level principal.

System_CAPS_ICON_important.jpg 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 permissionImplied by database permissionImplied by server permission
ALTERCONTROLALTER ANY DATABASE
ALTER ANY APPLICATION ROLEALTERCONTROL SERVER
ALTER ANY ASSEMBLYALTERCONTROL SERVER
ALTER ANY ASYMMETRIC KEYALTERCONTROL SERVER
ALTER ANY CERTIFICATEALTERCONTROL SERVER
ALTER ANY COLUMN ENCRYPTION KEYALTERCONTROL SERVER
ALTER ANY COLUMN MASTER KEY DEFINITIONALTERCONTROL SERVER
ALTER ANY CONTRACTALTERCONTROL SERVER
ALTER ANY DATABASE AUDITALTERALTER ANY SERVER AUDIT
ALTER ANY DATABASE DDL TRIGGERALTERCONTROL SERVER
ALTER ANY DATABASE EVENT NOTIFICATIONALTERALTER ANY EVENT NOTIFICATION
ALTER ANY DATABASE EVENT SESSION

 Applies to: Azure SQL Database.
ALTERALTER ANY EVENT SESSION
ALTER ANY DATABASE SCOPED CONFIGURATION

 Applies to: SQL Server 2016 through SQL Server 2016, SQL Database.
CONTROLCONTROL SERVER
ALTER ANY DATASPACEALTERCONTROL SERVER
ALTER ANY EXTERNAL DATA SOURCEALTERCONTROL SERVER
ALTER ANY EXTERNAL FILE FORMATALTERCONTROL SERVER
ALTER ANY FULLTEXT CATALOGALTERCONTROL SERVER
ALTER ANY MASKCONTROLCONTROL SERVER
ALTER ANY MESSAGE TYPEALTERCONTROL SERVER
ALTER ANY REMOTE SERVICE BINDINGALTERCONTROL SERVER
ALTER ANY ROLEALTERCONTROL SERVER
ALTER ANY ROUTEALTERCONTROL SERVER
ALTER ANY SCHEMAALTERCONTROL SERVER
ALTER ANY SECURITY POLICY

 Applies to: Azure SQL Database.
CONTROLCONTROL SERVER
ALTER ANY SERVICEALTERCONTROL SERVER
ALTER ANY SYMMETRIC KEYALTERCONTROL SERVER
ALTER ANY USERALTERCONTROL SERVER
AUTHENTICATECONTROLAUTHENTICATE SERVER
BACKUP DATABASECONTROLCONTROL SERVER
BACKUP LOGCONTROLCONTROL SERVER
CHECKPOINTCONTROLCONTROL SERVER
CONNECTCONNECT REPLICATIONCONTROL SERVER
CONNECT REPLICATIONCONTROLCONTROL SERVER
CONTROLCONTROLCONTROL SERVER
CREATE AGGREGATEALTERCONTROL SERVER
CREATE ASSEMBLYALTER ANY ASSEMBLYCONTROL SERVER
CREATE ASYMMETRIC KEYALTER ANY ASYMMETRIC KEYCONTROL SERVER
CREATE CERTIFICATEALTER ANY CERTIFICATECONTROL SERVER
CREATE CONTRACTALTER ANY CONTRACTCONTROL SERVER
CREATE DATABASECONTROLCREATE ANY DATABASE
CREATE DATABASE DDL EVENT NOTIFICATIONALTER ANY DATABASE EVENT NOTIFICATIONCREATE DDL EVENT NOTIFICATION
CREATE DEFAULTALTERCONTROL SERVER
CREATE FULLTEXT CATALOGALTER ANY FULLTEXT CATALOGCONTROL SERVER
CREATE FUNCTIONALTERCONTROL SERVER
CREATE MESSAGE TYPEALTER ANY MESSAGE TYPECONTROL SERVER
CREATE PROCEDUREALTERCONTROL SERVER
CREATE QUEUEALTERCONTROL SERVER
CREATE REMOTE SERVICE BINDINGALTER ANY REMOTE SERVICE BINDINGCONTROL SERVER
CREATE ROLEALTER ANY ROLECONTROL SERVER
CREATE ROUTEALTER ANY ROUTECONTROL SERVER
CREATE RULEALTERCONTROL SERVER
CREATE SCHEMAALTER ANY SCHEMACONTROL SERVER
CREATE SERVICEALTER ANY SERVICECONTROL SERVER
CREATE SYMMETRIC KEYALTER ANY SYMMETRIC KEYCONTROL SERVER
CREATE SYNONYMALTERCONTROL SERVER
CREATE TABLEALTERCONTROL SERVER
CREATE TYPEALTERCONTROL SERVER
CREATE VIEWALTERCONTROL SERVER
CREATE XML SCHEMA COLLECTIONALTERCONTROL SERVER
DELETECONTROLCONTROL SERVER
EXECUTECONTROLCONTROL SERVER
INSERTCONTROLCONTROL SERVER
KILL DATABASE CONNECTION

 Applies to: Azure SQL Database.
CONTROLALTER ANY CONNECTION
REFERENCESCONTROLCONTROL SERVER
SELECTCONTROLCONTROL SERVER
SHOWPLANCONTROLALTER TRACE
SUBSCRIBE QUERY NOTIFICATIONSCONTROLCONTROL SERVER
TAKE OWNERSHIPCONTROLCONTROL SERVER
UNMASKCONTROLCONTROL SERVER
UPDATECONTROLCONTROL SERVER
VIEW ANY COLUMN ENCRYPTION KEY DEFINITIONCONTROLVIEW ANY DEFINITION
VIEW ANY COLUMN MASTER KEY DEFINITIONCONTROLVIEW ANY DEFINITION
VIEW DATABASE STATECONTROLVIEW SERVER STATE
VIEW DEFINITIONCONTROLVIEW ANY DEFINITION

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_principalAdditional permission required
Database userIMPERSONATE 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 loginIMPERSONATE 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 GroupMembership 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 certificateMembership 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 keyMembership 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 principalIMPERSONATE 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 roleALTER 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 roleALTER 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.

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 AdventureWorks2012 database to application role AuditMonitor.

Applies to: SQL Server 2008 through SQL Server 2016, SQL Database V12.
USE AdventureWorks2012;  
GRANT SHOWPLAN TO AuditMonitor;  
GO  

C. Granting CREATE VIEW with GRANT OPTION

The following example grants CREATE VIEW permission on the AdventureWorks2012 database to user CarmineEs with the right to grant CREATE VIEW to other principals.

USE AdventureWorks2012;  
GRANT CREATE VIEW TO CarmineEs WITH GRANT OPTION;  
GO  

sys.database_permissions (Transact-SQL)
sys.database_principals (Transact-SQL)
CREATE DATABASE (SQL Server Transact-SQL)
GRANT (Transact-SQL)
Permissions (Database Engine)
Principals (Database Engine)

Community Additions

ADD
Show:
© 2016 Microsoft