|
|
GRANT to user/role
Grant permissions to a database user, database role, or application role.
Syntax
GRANT permission [ ,...n ]
TO grantee_principal [,...n ]
[WITH GRANT OPTION]
[AS grantor_principal]
GRANT permission | ALL [ PRIVILEGES ] [,...n ]
[ON USER::user | ON ROLE::role | ON APPLICATION ROLE::role]
TO principal [ ,...n ] [WITH GRANT OPTION]
[AS principal ]
Key:
principal one of:
user /role /application role
user mapped to a Windows login/group/certificate
user mapped to an asymmetric key
user not mapped to a server principal.
grantee_principal/grantor_principal
SQL Server login or SQL Login mapped to a Windows login/group/certificate/asymmetric_key
The grantor_principal for an Endpoint must be a SQL Server login
WITH GRANT Also allow the principal to grant this permission to other principals.
permission
ALL (= BACKUP DATABASE, BACKUP LOG, CREATE DATABASE, CREATE DEFAULT,
CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW)
ALTER
ALTER ANY APPLICATION ROLE
ALTER ANY ASSEMBLY
CREATE AGGREGATE
CREATE ASSEMBLY
UNSAFE ASSEMBLY
EXTERNAL ACCESS ASSEMBLY
CREATE ASYMMETRIC KEY
ALTER ANY ASYMMETRIC KEY
AUTHENTICATE
AUTHENTICATE SERVER
BACKUP DATABASE
BACKUP LOG
ADMINISTER BULK OPERATIONS
ALTER ANY CONNECTION
ALTER ANY CREDENTIAL
CHECKPOINT
CONNECT
CONNECT REPLICATION
CONTROL
CREATE CERTIFICATE
ALTER ANY CERTIFICATE
CREATE CONTRACT
ALTER ANY CONTRACT
VIEW DATABASE STATE
CREATE DATABASE
CREATE ANY DATABASE
ALTER ANY DATABASE
VIEW ANY DATABASE
CREATE DDL EVENT NOTIFICATION
CREATE DATABASE DDL EVENT NOTIFICATION
CREATE TRACE EVENT NOTIFICATION
ALTER ANY DATABASE EVENT NOTIFICATION
ALTER ANY DATASPACE
CREATE DEFAULT
VIEW DEFINITION
VIEW ANY DEFINITION
DELETE
CREATE ENDPOINT
ALTER ANY ENDPOINT
ALTER ANY EVENT NOTIFICATION
EXECUTE
CREATE FULLTEXT CATALOG
ALTER ANY FULLTEXT CATALOG
CREATE FUNCTION
INSERT
ALTER ANY LINKED SERVER
ALTER ANY LOGIN
CREATE MESSAGE TYPE
ALTER ANY MESSAGE TYPE
TAKE OWNERSHIP
CREATE PROCEDURE
CREATE QUEUE
SUBSCRIBE QUERY NOTIFICATIONS
ALTER RESOURCES
REFERENCES
CREATE REMOTE SERVICE BINDING
ALTER ANY REMOTE SERVICE BINDING
CREATE ROLE
ALTER ANY ROLE
CREATE ROUTE
ALTER ANY ROUTE
CREATE RULE
CREATE SCHEMA
ALTER ANY SCHEMA
SELECT
ALTER SERVER STATE
VIEW SERVER STATE
ALTER SETTINGS
CREATE SERVICE
ALTER ANY SERVICE
SHOWPLAN
SHUTDOWN
CONNECT SQL
CREATE SYMMETRIC KEY
ALTER ANY SYMMETRIC KEY
CREATE SYNONYM
CREATE TABLE
ALTER TRACE
ALTER ANY DATABASE DDL TRIGGER
CREATE TYPE
ALTER ANY USER
UPDATE
CREATE VIEW
CREATE XML SCHEMA COLLECTION
If CONTROL SERVER or ALTER ANY SERVER STATE are granted to a user, then many of the permissions above will be implicitly granted.
When granting from one principle to another principle (GRANT permission ON user/role TO user/role ) then the only valid permissions are:
CONTROL, IMPERSONATE, TAKE OWNERSHIP, ALTER, VIEW DEFINITION
For user: CONTROL/IMPERSONATE/ALTER/VIEW DEFINITION
For role: CONTROL/TAKE OWNERSHIP/ALTER/VIEW DEFINITION
For app. role: CONTROL/ALTER/VIEW DEFINITION
Examples
USE MyDb;
GRANT CREATE TABLE TO MyUser; GRANT CREATE VIEW TO MyUser WITH GRANT OPTION;
GO GRANT VIEW DEFINITION ON ROLE::SupervisorRole
TO User64 WITH GRANT OPTION; GO GRANT IMPERSONATE ON USER::User78 TO SupervisorRole;
GO
"I had one guy at a gas station in New York say to me, 'Hey, you look like Hugh Grant. No offense'" ~ Hugh Grant, on being recognized in public
Related commands:
REVOKE User/Role permissions
GRANT Object permissions
DENY User/Role permissions
sys.database_permissions
sys.database_principals
Equivalent Oracle command:
