grant

Assigns permissions to individual users, groups of users, and roles.

Syntax

Grants permission to access database objects:
grant {all [privileges] | permission_list} 
	on {table_name as [correlation_name][(column_list)]
		| view_name[(column_list)] 
		| stored_procedure_name | SQL_function_name}
		| keyname}
	[where search_conditions [as pred_name]]
	to {public | name_list | role_list}
	[with grant option]
	[granted by grantor]
Grants permission to use built-in functions:
grant select 
	on [builtin] builtin 
	to {name_list | role_list}
	[granted by grantor]
Grants system privileges to execute certain commands:
grant {all [privileges] | privilege_list} 
	to {public | name_list | role_list}
	[granted by grantor]
Grants dbcc privileges:
grant {dbcc_privilege [on database ]
		[, dbcc_privilege [on database ], ...]} 
	to {user_list | role_list }
	[granted by grantor]
Grants the default permissions for specific system tables:
grant default permissions on system tables
Grants permission that allows grantee to switch server user identity to any other server login and limit its use based on the target login’s roles:
grant set proxy to name_list
	[restrict role role_list | all | system]
	[granted by grantor]

Parameters

Examples

Usage

See also:
  • proc_role, show_role in Reference Manual: Building Blocks

  • sp_column_privileges, sp_table_privileges, sp_addgroup, sp_adduser, sp_changedbowner, sp_changegroup, sp_dropgroup, sp_dropuser, sp_helpgroup, sp_helprotect, sp_helpuser in Reference Manual: Procedures

  • For more information on revoking a privilege from public or a group, see How SAP ASE Saves Predicated Privileges in sysprotects in the Security Administration Guide

Standards

ANSI SQL – Compliance level: Entry-level compliant. grant dbcc is also a Transact-SQL extension.

grant dbcc, and granting permissions to groups and granting set proxy are Transact-SQL extensions. Granting set session authorization (identical in function to set proxy) follows the ANSI standard.

Permissions

The permission checks for grant differ based on your granular permissions settings.

SettingDescription
Enabled
With granular permissions enabled, in general, grant can be executed by a user with one of the following privilege management privileges, depending the privilege or permission being granted. For:
  • Server-wide privileges – you must be a user with manage server permissions privilege or manage security permissions privilege.

  • Database-wide privileges – you must be a user with manage database permissions privilege.

  • Object privileges – you must be the object owner or a user with manage any object permission privilege

To execute grant default, you must be the database owner or a user with own database privilege on the database.
Disabled

With granular permissions disabled, grantable system privileges are limited to create database, create default, create function, create procedure, create rule, create table, create view, connect, set proxy, and set tracing

Command execution – only system administrators can grant create database, connect, and set tracing permissions, and only from the master database. Only system security officers can grant create trigger permission.

To execute grant default, you must be the database owner or a user with sa_role.

For:
  • Database consistency checking – only system administrators can run grant dbcc commands.

  • Database object grant access – permission for database objects defaults to object owners. Object owners can grant permission to other users on their own database objects.

  • Functions – only system administrators can grant permissions on built-in functions.

  • Encrypted columns – only the systems security officer and the key custodian have implicit permission to create encryption keys.

  • Proxy and session authorization – only system security officers can grant set proxy or set session authorization, and only from the master database. Granting permission to execute set proxy or set session authorization allows the grantee to impersonate another login in the server. set proxy and set session authorization are identical, except that set session authorization follows the ANSI92 standard, and set proxy is a Transact-SQL extension.

  • System tables – Database owners can grant default permissions on system tables.

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

40

Audit option

grant

Command or access audited

grant

Information in extrainfo
  • Roles – current active roles

  • Keywords or options – Full command text of the grant statement

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

Related reference
create role
revoke
setuser
set
create table