Privileges for grant

Privileges for grant differ based on the level at which you are working.

This table lists all grantable server-wide system privileges. Server-wide privileges must be granted in the master database. For operations each privilege is authorized to perform, see Using Granular Permissions of the Security Administration Guide.

Note: In the following list, when granular permissions is disabled, only privileges marked with an asterisk ( * ) can be granted.

dbcc privilege syntax dbcc dbcc_subcmd on all is alias to dbcc dbcc_subcmd any database. Both syntaxes are supported.

Grantable Server-Wide System Privileges
Category	Description
Privilege Management	`manage security permissions` `manage server permissions`
Audit Management	`manage auditing`
Login and Role Management	`allow exceptional login` `change password` `manage any login` `manage any login profile` `manage remote login` `manage roles`
Database Management	`checkpoint` (on `database`) `checkpoint any database` `create database`* `dump any database` `dump database` (on `database`) `load any database` `load database` (on `database`) `manage any database` `mount any database` `online any database` `online database` (on `database`) `own any database` `own database` (on `database`) `quiesce any database` `unmount any database`
Server Management	`manage any thread pool` `manage cluster` `manage disk` `manage security configuration` `manage server` `manage server configuration` `shutdown`
DBCC Privilege	`dbcc checkalloc any database`* `dbcc checkcatalog any database`* `dbcc checkdb any database`* `dbcc checkindex any database`* `dbcc checkstorage any database`* `dbcc checktable any database`* `dbcc checkverify any database`* `dbcc fix_text any database`* `dbcc indexalloc any database`* `dbcc reindex any database`* `dbcc tablealloc any database`* `dbcc textalloc any database`* `dbcc tune`*
Application Management	`manage any execution class` `manage any ESP` `manage data cache` `manage dump configuration` `manage lock promotion threshold` `monitor qp performance` `manage resource limit`
Others	`connect`* `kill` `kill any process` `map external file` `monitor server replication` `set proxy` `set tracing`* `set tracing any process` `set switch` `show switch` `use any database` `use database`

This table lists all grantable database-wide system privileges. Database-wide privilege must be granted in the database for which the privilege is intended to be exercised. For operations each privilege is authorized to perform, see Using Granular Permissions in the Security Administration Guide.

Note: In the following table, when granular permissions is disabled, only privileges marked with an asterisk ( * ) can be granted.

Grantable Database-Wide Privileges
Category	Privilege
Privilege Management	`manage any object permission` `manage database permissions`
User Management	`manage any user`
Set User	`setuser` *
Replication Management	`manage replication`
Database Management	`manage database`
Query Plan Management	`manage abstract plans`
DBCC Privilge	`dbcc checkalloc` * `dbcc checkcatalog` * `dbcc checkdb` * `dbcc checkindex` * `dbcc checkstorage` * `dbcc checktable` * `dbcc checkverify` * `dbcc fix_text` * `dbcc indexalloc` * `dbcc reindex` * `dbcc textalloc` * `manage checkstorage` `dbcc tablealloc` * `report checkstorage`
System Catalog	`select any audit table` `select any system catalog` `truncate any audit table`
General Object	`alter any object owner` `create any object` `drop any object`
Encryption Key	`create encryption key` * `manage any encryption key` `manage column encryption key` `manage master key` `manage service key`
Default	`create default` * `create any default` `drop any default`
Function	`create function` * `create any function` `drop any function` `execute any function`
Index	`create any index`
Procedure	`create procedure` * `create any procedure` `execute any procedure` `drop any procedure`
Rule	`create rule` * `create any rule` `drop any rule`
Table	`alter any table` `create any table` `create table` * `decrypt any table` `delete any table` `drop any table` `identity_insert any table` `identity_update any table` `insert any table` `manage any statistics` `references any table` `reorg any table` `select any table` `transfer any table` `truncate any table` `update any table`
Trigger	`create trigger` * `create any trigger` `drop any trigger`
View	`create view` * `create any view` `drop any view`

This table lists all grantable privileges and permissions in alphabetic order. Privileges indicated with a “*” do not require that you enable granular permissions.

Alphabetical Listing of Privileges
Privilege Name	Privilege Type	Managed by (when Granular Permissions is Enabled)	Implied by
`allow exceptional login`	server	manage server permissions
`alter any object owner`	database	manage database permissions
`alter any table`	database	manage database permissions
`change password`	server	manage security permissions
`checkpoint any database`	server	manage server permissions
`checkpoint` (on database)	server	manage server permissions	`checkpoint any database`
`checkpoint` (on `sybsecurity`)	server	manage security permissions
`connect`*	server	manage server permissions
`create any default`	database	manage database permissions	`create any object`
`create any function`	database	manage database permissions	`create any object`
`create any index`	database	manage database permissions	`create any object`
`create any object`	database	manage database permissions
`create any procedure`	database	manage database permissions	`create any object`
`create any rule`	database	manage database permissions	`create any object`
`create any table`	database	manage database permissions	`create any object`
`create any trigger`	database	manage database permissions	`create any object`
`create any view`	database	manage database permissions	`create any view`
`create database`*	server	manage database permissions
`create default`*	database	manage database permissions	`create any default`
`create encryption key`*	database	manage security permissions	`manage column encryption key`
`create function`*	database	manage database permissions	`create any function`
`create index`*	database	manage database permissions	`create any index`
`create procedure`*	database	manage database permissions	`create procedure`
`create rule`*	database	manage database permissions	`create any rule`
`create table`*	database	manage database permissions	`create any table`
`create trigger`*	database	manage database permissions	`create any trigger`
`create view`*	database	manage database permissions	`create any view`
`dbcc checkalloc`*	database	manage database permissions	`dbcc checkalloc any database`
`dbcc checkalloc any database`*	server	manage server permissions
`dbcc checkcatalog`*	database	manage database permissions	`dbcc checkcatalo` `any database`
`dbcc checkcatalog any database`*	server	manage server permissions
`dbcc checkdb`*	database	manage database permissions	`dbcc checkdb` `any database`
`dbcc checkdb any database`*	server	manage server permissions
`dbcc checkindex`*	database	manage database permissions	`dbcc checkindex` `any database`
`dbcc checkindex any database` *	server	manage server permissions
`dbcc checkstorage`*	database	manage database permissions	`dbcc checkstorage` `any database`
`dbcc checkstorage any database` *	server	manage server permissions
`dbcc checktable`*	database	manage database permissions	`dbcc checktable` `any database`
`dbcc checktable any database`*	server	manage server permissions
`dbcc checkverify`*	database	manage database permissions	`dbcc checkverify` `any database`
`dbcc checkverify any database`*	server	manage server permissions
`dbcc fix_text`*	database	manage database permissions	`dbcc fix_text` `any database`
`dbcc fix_text any database`*	server	manage server permissions
`dbcc indexalloc`*	database	manage database permissions	`dbcc indexalloc` `any database`
`dbcc indexalloc any database`*	server	manage server permissions
`dbcc reindex`*	database	manage database permissions	`dbcc reindex` `any database`
`dbcc reindex any database`*	server	manage server permissions
`dbcc tablealloc`*	database	manage database permissions	`dbcc tablealloc` `any database`
`dbcc tablealloc any database`*	server	manage server permissions
`dbcc textalloc`*	database	manage database permissions	`dbcc textalloc` `any database`
`dbcc textalloc any database`*	server	manage server permissions
`dbcc tune`*	server	manage server permissions
`decrypt`*	object (column)	manage any object permission/object owner	`decrypt any table`
`decrypt any table`	database	manage database permissions
`delete`*	object	manage any object permission/object owner	`delete any table`
`delete any table`	database	manage database permissions
`delete statistics`*	object	manage any object permission/object owner	`manage any statistics`
`drop any default`	database	manage database permissions	`drop any object`
`drop any function`	database	manage database permissions	`drop any object`
`drop any object`	database	manage database permissions
`drop any procedure`	database	manage database permissions	`drop any object`
`drop any rule`	database	manage database permissions	`drop any object`
`drop any table`	database	manage database permissions	`drop any object`
`drop any trigger`	database	manage database permissions	`drop any object`
`drop any view`	database	manage database permissions	`drop any object`
`dump any database`	server	manage server permissions
`dump database` (on database)	server	manage server permissions	`dump any database`
`dump database` (on `sybsecurity`)	server	manage security permissions
`execute`*	object	manage any object permission/object owner	`execute any function` (for udf) `execute any procedure` (for system procedures)
`execute any function`	database	manage database permissions
`execute any procedure`	database	manage database procedures
`identity_insert`	object	manage any object permission/object owner
`identity_insert any table`	database	manage database permissions
`identity_update`	object	manage any object permission/object owner
`identity_update any table`	database	manage database permissions
`insert`*	object	manage any object permission/object owner	`insert any table`
`insert any table`	database	manage database permissions
`kill`	server	manage server permissions	`kill any process`
`kill any process`	server	manage server permissions
`load any database`	server	manage server permissions
`load database` (on database)	server	manage server permissions	`load any database`
`load database` (on `sybsecurity`)	server	manage security permissions
`manage abstract plans`	database	manage database permissions
`manage any database`	server	manage server permissions
`manage any encryption key`	database	manage security permissions
`manage any ESP`	server	manage server permissions
`manage any execution class`	server	manage server permissions
`manage any login`	server	manage security permissions
`manage any login profile`	server	manage security permissions
`manage any remote login`	server	manage security permissions
`manage any statistics`	database	manage database permissions
`manage any thread pool`	server	manage server permissions
`manage any user`	database	manage database permissions
`manage auditing`	server	manage security permissions
`manage checkstorage`	database	manage database permissions
`manage cluster`	server	manage server permissions
`manage column encryption key`	database	manage security permissions	`manage any encryption key`
`manage data cache`	server	manage server permissions
`manage database`	database	manage database permissions	`manage any database`
`manage database permissions`	database	manage security permissions
`manage disk`	server	manage server permissions
`manage dump configuration`	server	manage server permissions
`manage lock promotion threshold`	server	manage server permissions
`manage master key`	database	manage security permissions	`manage any encryption key`
`manage replication`	server	manage server permissions
`manage resource limit`	server	manage server permissions
`manage roles`	server	manage security permissions
`manage security configuration`	server	manage security permissions
`manage security permissions`	server	manage security permissions
`manage server`	server	manage server permissions
`manage server configuration`	server	manage server permissions
`manage server permissions`	server	manage server permissions
`manage service key`	database	manage security permissions	`manage any encryption key`
`map external file`	server	manage server permissions
`monitor qp performance`	server	manage server permissions
`monitor server replication`	server	manage server permissions
`mount any database`	server	manage server permissions
`own any database`	server	manage server permissions
`online any database`	server	manage server permissions
`online database` (on database)	server	manage server permissions	`online any database`
`online database` (on `sybsecurity`)	server	manage security permissions
`own database` (on database)	server	manage server permissions
`own database` (on `sybsecurity`)	server	manage security permissions
`quiesce any database`	server	manage server permissions
`references`*	object (column)	manage any object permission/object owner	`references any table`
`references any table`	database	manage database permissions
`report checkstorage`	database	manage database permissions
`reorg any table`	database	manage database permissions
`select`*	object (column)	manage any object permission/object owner	`select any table` (for user tables or views) `select any audit table` (for audit tables) `select any system catalog` (for system tables)
`select any audit table`	database	manage database permissions
`select any system catalog`	database	manage database permissions
`select any table`	database	manage database permissions
`set proxy`*	server	manage security permissions
`set switch`	server	manage server permissions
`set tracing`*	server	manage server permissions	`set tracing` `for any process`
`set tracing any process`	server	manage server permissions
`setuser`*	database	manage database permissions
`show switch`	server	manage server permissions
`shutdown`	server	manage server permissions
`transfer any table`	database	manage database permissions
`transfer table`	object	manage any object permission/object owner	`transfer any table`
`truncate any audit table`	database	manage database permissions
`truncate any table`	database	manage database permissions
`truncate table`*	object	manage any object permission/object owner	`truncate any table`
`unmount any database`	server	manage server permissions
`update`*	object (column)	manage any object permission/object owner	`update any table`
`update any security catalog`	server	manage security permissions
`update any table`	database	manage database permissions
`update statistics`	object	manage any object permission/object owner	`manage any statistics`
`use any database`	server	manage server permissions
`use database` (on database)	server	manage server permissions	`use any database`
`use database` (on sybsecurity)	server	manage security permissions

Possessing one privilege may imply possessing another, more granular privilege. For example, a user with select any table privilege implies that the user has select permission on all user tables.
When granting the following database management privileges, the on database clause must be specified for each privilege: checkpoint, dump database, load database, online database, own database. For example, to grant dump database privilege on db1 to smith, you can use:
```
grant dump database on db1 to smith
```
You can grant different database management privileges on different databases in the same grant command. For example, to grant own database on db1 and load database on db2 to smith, you can use:
```
grant own database on db1, load database on db2 to smith 
```
You can grant permissions only on objects in your current database.
grant and revoke commands are order-sensitive. The command that takes effect when there is a conflict is the one issued most recently.
A user can be granted permission on a view or stored procedure even if he or she has no permissions on objects referenced by the procedure or view. See Managing User Permissions in the Security Administration Guide.
The SAP ASE server grants all users permission to declare cursors, regardless of the permissions defined for the base tables or views referenced in the declare cursor statement. Cursors are not defined as SAP ASE objects (such as tables), so no permissions can be applied against a cursor. When a user opens a cursor, the SAP ASE server determines whether the user has select permissions on the objects that define that cursor’s result set. It checks permissions each time a cursor is opened.

If the user has permission to access the objects defined by the cursor, the SAP ASE server opens the cursor and allows the user to fetch row data through the cursor. The SAP ASE server does not apply permission checking for each fetch. However, if the user performs a delete or an update through that cursor, the regular permission checking applies for deleting and updating the data of objects referenced in the cursor result set.
A grant statement adds one row to the sysprotects system table for each user, group, or role that receives the permission. If you subsequently revoke the permission from the user or group, the SAP ASE server removes the row from sysprotects. If you revoke the permission from selected group members only, but not from the entire group to which it was granted, the SAP ASE server retains the original row and adds a new row for the revoked permission.
A user, group, or role can be granted the same privilege or permission by different grantors. In this situation, there are multiple rows in sysprotects that represents multiple grants on the same privilege or permissions. If one or more than one grants are later revoked, the user, group, or role may still have the privilege or permission if there is one grant remain unrevoked.
If a user inherits a particular permission by virtue of being a member of a group, and the same permission is explicitly granted to the user, no row is added to sysprotects. For example, if “public” has been granted select permission on the phone column in the authors table, then John, a member of “public,” is granted select permission on all columns of authors. The row added to sysprotects as a result of the grant to John contains references to all columns in the authors table except for the phone column, on which he already had permission.
By default, permission to issue the create trigger command is granted to users. When you revoke permission for a user to create triggers, a revoke row is added in the sysprotects table for that user. To grant permission to that user to issue create trigger, you must issue two grant commands. The first command removes the revoke row from sysprotects; the second inserts a grant row. If you revoke permission to create triggers, the user cannot create triggers even on tables that the user owns. Revoking permission to create triggers from a user affects only the database where the revoke command was issued.
Use these system procedures to display information about permissions:
- sp_helprotect reports permissions information for database objects, users, groups, and roles.
- sp_column_privileges reports permissions information for one or more columns in a table or view.
- sp_table_privileges reports permissions information for all columns in a table or view.
- sp_activeroles displays all active roles—and all roles contained by those roles—for the current login session of the SAP ASE server.
- sp_displayroles displays all roles granted to another role or user, or displays the entire hierarchy tree of roles in table format.

You can view permissions using sp_helprotect:

1> use pubs2
2> go
1> sp_helprotect
2> go

grantor  grantee  type   action   object         column      grantable
-------  -------  ----   ------   -------        -------     ---------
dbo     public    Grant  Select   sysalternates  All          FALSE
...
dbo     Walter    Grant  DBCC     DBCC           dbcc checkdb FALSE

 (1 row affected)
 (return status = 0)

You cannot use the grant with grant option with grant dbcc.