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: GRANT


 
Copyright © 1999-2024 SS64.com
Some rights reserved