grant

Description

Assigns permissions to individual users, groups of users, and roles. Assigns roles to users or system or user-defined roles.

Syntax

Grants permission to access database objects:

grant {all [privileges] | permission_list} 
	on {table_name [(column_list)]
		| view_name[(column_list)] 
		| stored_procedure_name}
		| function_name
		| keyname}
	to {public | name_list | role_list}
	[with grant option]

Grants permission to use built-in functions:

grant select 
	on [builtin] builtin 
	to {name_list | role_list}

Grants permission to execute certain commands:

grant {all [privileges] | command_list} 
	to {public | name_list | role_list}

Grants access on certain dbcc commands:

grant dbcc {dbcc_command [on {all | database}]
		[, dbcc_command [on {all | database}], ...]} 
	to {user_list | role_list }

Grants permission to create encryption keys:

grant create encryption key to {user_list | role_list  | group_list}

Grants decrypt permission on a table or a list of columns in a table.

grant decrypt on [ owner. ]tablename[(columnname [{,columname}])] 
	to {user | group | role}

Grants the default permissions for specific system tables:

grant default permissions on system tables

Grants a role to a user or a role:

grant {role role_granted [, role_granted ...]}
	to grantee [, grantee...]

Switches your server user identity to any other server login and limit its use based on the target login roles:

grant set {proxy | tracing} to role_list
	[restrict role role_list | all | system]

Parameters

all

when used to assign permission to access database objects (the first syntax format), all specifies that all permissions except decrypt applicable to the specified object are granted. All object owners can use grant all with an object name to grant permissions on their own objects. You must grant decrypt permissions separately.

Only a System Administrator or the Database Owner can assign permission to create database objects (the third syntax format). When used by a System Administrator, grant all assigns all create permissions (create database, create default, create procedure, create rule, create table, and create view). When the database owner uses grant all, or executes the grant all outside the master database, Adaptive Server grants all create permissions except create databaseand connect and prints an informational message.

Specifying all does not include permission to execute set proxy or set session authorization, create trigger, and create encryption key. The system security officer must grant these explicitly.

When used to grant set proxy to role_list, restricts the grantee from being granted any new roles when switching identities.

permission_list

is a list of object access permissions granted. If more than one permission is listed, separate them with commas. The following table illustrates the access permissions that can be granted on each type of object:

Object

permission_list can include

Table

select, insert, delete, update, references, update statistics, delete statistics, truncate table, decrypt

View

select, insert, delete, update, decrypt

Column

select, update, references, decrypt

Column names can be specified in either permission_list or column_list.

Stored procedure

execute

Encryption key

select

table_name

is the name of the table on which you are granting permissions. The table must be in your current database. Only one object can be listed for each grant statement.

column_list

is a list of columns, separated by commas, to which the permissions apply. If columns are specified, only select, references, and update permissions can be granted.

view_name

is the name of the view on which you are granting permissions. The view must be in your current database. Only one object can be listed for each grant statement.

key_name

is the name of an encryption key on which you are granting access. The table must be in your current database. Only one object can be listed for each grant statement.

stored_procedure_name

is the name of the stored procedure on which you are granting permissions. The stored procedure must be in your current database. Only one object can be listed for each grant statement.

public

is all users. For object access permissions, public excludes the object owner. For object creation permissions or set proxy authorizations, public excludes the Database Owner. You cannot grant permissions with grant option to “public” or to other groups or roles.

name_list

is a list of users’ database names and group names, separated by commas.

role_list

is a list of roles—either system-defined or user-defined—to which you are granting the permission. If grantees do not have the roles in the role_list already granted to them, set proxy to the target login fails if the target login has any roles in the role_list granted.

role_list cannot be a variable.

NoteYou cannot grant or revoke dbcc commands to public or groups.

with grant option

allows the users specified in name_list to grant object access permissions to other users. You can grant permissions with grant option only to individual users, not to “public” or to a group or role.

builtin

is a built-in function. Specifying built-in functions allows you to differentiate between a table and a grantable built-in function with the same name. The functions are set_appcontext, get_appcontext, list_appcontext, and rm_appcontext.

command_list

is a list of commands that the user can execute. If more than one command is listed, separate them with commas. The command list can include create database, create default, create procedure, create rule, create table, create view, set proxy, set session authorization, create encryption key, and connect.

create database and set tracing permission can be granted only by a system administrator, and only from within the master database. Only a system security officer can grant permission to exectue create encryption key and create trigger.

Only a system security officer can grant users permission to execute set proxy or set session authorization. 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.

dbcc_command

is the name of the dbcc command you are granting. It cannot be a variable. Table 1-25 lists the valid grant dbcc commands.

database

is the name of the database on which you are granting permissions. It is used with database-specific dbcc commands to grant permission only on the target database. The grantee must be a valid user in the target database. database conforms to the rules for identifiers and cannot be a variable.

If there are multiple granted actions in the same command, database must be unique.

See “on all | database parameter and server-level commands” for more information.

role

grants a role to a user or to a system or user-defined role.

role_granted

is the name of a system or user-defined role that the System Security Officer is granting to a user or a role.

grantee

is the name of a system role, user-defined role, or a user, to whom you are granting a role.

role_list

is a list of system-defined or user-defined roles to which you are granting the permission.

proxy

Grants permission for a user to impersonate another user. Only the system security officer can grant set proxy.

tracing

Grants permission for a user to enable or disable tracing for the set option, set plan, and dbcc traceon or traceoff. Only the system administrator can grant set tracing permission, and only from the master database. Once the user is granted permission to set tracing, they do not need to be in the master database to run the tracing related set options and trace flags 3604 and 3605.

default permissions on system tables

specifies that you grant the default permissions for the system tables listed in “granting default permissions on system tables”.

system

ensures that the grantee has the same set of system roles as the target login.

Examples

Example 1

Grants Mary and the “sales” group permission to use the insert and delete commands on the titles table:

grant insert, delete
on titles
to mary, sales

Example 2

Grants select permission on the get_appcontext function to “public” (which includes all users):

grant select on builtin get_appcontext to public 

Compare this to the following, which grants select permission on a table called get_appcontext, if a table with that name exists:

grant select on get_appcontext to public

Specifically including the builtin argument in your grant statement ensures that you do not mistakenly select a table that has the same name as a function—in this example, the get_appcontext function versus a table called get_appcontext.

Example 3

Two ways to grant update permission on the price and advance columns of the titles table to “public” (which includes all users):

grant update
on titles (price, advance)
to public

or:

grant update (price, advance)
on titles 
to public

Example 4

Grants transfer table permission to user mary for the titles table:

grant transfer table on titles to mary

Example 5

Grants Harry and Billy permission to execute either set proxy or set session authorization to impersonate another user in the server:

grant set proxy to harry, billy

Example 6

Grants users with sso_role permission to execute either set proxy or set session authorization to impersonate another user in the server:

grant set session authorization to sso_role

Example 7

Grants users with vip_role the ability to impersonate another user in the server. vip_role must be a role defined by a System Security Officer with the create role command:

grant set proxy to vip_role

Example 8

Grants Mary and John permission to use the create database and create table commands. Because create database permission is being granted, this command can be executed only by a System Administrator within the master database. Mary and John’s create table permission applies only to the master database:

grant create database, create table
to mary, john

Example 9

Grants complete access permissions, except decrypt permission, on the titles table to all users:

grant all on titles 
to public

Example 10

Grants all object creation permissions, except create encryption key, in the current database to all users. If this command is executed by a System Administrator from the master database, it includes create database permission:

grant all
to public

Example 11

Gives Mary permission to use the update command on the authors table and to grant that permission to others:

grant update on authors
to mary
with grant option

Example 12

Gives Bob permission to use the select and update commands on the price column of the titles table and to grant that permission to others:

grant select, update on titles (price)
to bob
with grant option

Example 13

Grants permission to execute the new_sproc stored procedure to all System Security Officers:

grant execute on new_sproc
to sso_role

Example 14

Grants James permission to create a referential integrity constraint on another table that refers to the price column of the titles table:

grant references on titles (price)
to james

NoteBefore you create a table that includes a referential integrity constraint to reference another user’s table, you must be granted references permission on that referenced table. The table must also include a unique constraint or unique index on the referenced columns. See create table for more information about referential integrity constraints.

Example 15

Grants the database owner permission to specify column encryption using the ssn_key, when executed by the key owner.. The database owner requires select permission on ssn_key to reference it on create table, alter table or select into:

grant select on ssn_key to dbo

Example 16

Grants Bob permission to create encyption keys:

grant create encyption key to Bob

Example 17

Grants decrypt permission on all encrypted columns in the customer table:

grant decrypt on customer to accounts_role

Example 18

Grants the role “specialist”, with all its permissions and privileges, to the role “doctor”:

grant role specialist_role to doctor_role

Example 19

Grants the role “doctor” to Mary:

grant role doctor_role to mary

Example 20

On a user database called pubs2 owned by Jane, only Jane or the System Administrator can execute the dbcc checkdb command. Others encounter the following error:

1> dbcc checkdb (pubs2)
2> go
Msg 10302, Level 14, State 1:
Line 1:
Only the DBO of database 'test' or a user with System Administrator (SA) role can run this command. DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

Example 21

If Walter needs to be a maintenance user for pubs2 but the System Administrator does not want to grant him administrator-level privileges elsewhere, the System Administrator executes the following:

1> use pubs2 
2> go 
1> grant dbcc checkdb on pubs2 to walter
2> go

NoteThe System Administrator must be in the target database—in this case pubs2—and Walter must be a valid user in this target database.

Example 22

Walter (from the previous example) can now execute the dbcc checkdb command on the customers database without encountering an error:

%isql -Uwalter -Pwalterpassword -SSERVER
1> use pubs2
2> go
1> dbcc checkdb (pubs2)
2> go
Checking sysobjects: Logical pagesize is 2048 bytes
The total number of data pages in this table is 2.
Table has 27 data rows.
...
Table has 1 data rows.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

Example 23

Grants the use of dbcc to a role instead of a user. This lets System Administrators assign the ability to execute dbcc to individual users based on their role:

1> use master
2> go
1> create role checkdb_role
2> go
1> use pubs2
2> go
1> grant dbcc checkdb on pubs2 to checkdb_role
2> go

Next, the System Administrator grants the role to Joe:

1> sp_addlogin joe, joepassword
2> go
Password correctly set.
Account unlocked.
New login created.
 (return status = 0)
1> use pubs2
2> sp_adduser joe
3> go
1> grant role checkdb_role to joe
2> go

Joe can now execute the dbcc checkdb command on the pubs2 database when activating checkdb_role. Joe must be a valid user in pubs2:

% isql -Ujoe -Pjoepassword -SSERVER
1> use pubs2
2> go
1> dbcc checkdb (pubs2)
2> go
Msg 10302, Level 14, State 1:
Line 1:
Only the DBO of database 'pubs2' or a user with System Administrator (SA) role can run this command. DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
1> set role checkdb_role on
2> go
1> dbcc checkdb (pubs2)
2> go
Checking sysobjects: Logical pagesize is 2048 bytes
The total number of data pages in this table is 2.
...
The total number of data pages in this table is 1.
Table has 1 data rows. DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

Example 24

Through the use of a role, the System Administrator allows Carlos to run dbcc checkalloc on any database where he is a valid user, or where a database allows a “guest” user.

NoteYou do not need to add Carlos as an actual user in the master database if the user “guest” already exists in master.

1> use master
2> go
1> create role checkalloc_role
2> go
1> grant dbcc checkalloc on all to checkalloc_role
2> go
1> sp_addlogin carlos, carlospassword
2> go
1> grant role checkalloc_role to carlos
2> go

Example 25

Gives Frank, a valid user in the master database, the ability to execute dbcc checkdb for all databases in the server:

1> use master
2> go
1> sp_addlogin frank, frankpassword
2> go
Password correctly set.
Account unlocked.
New login created.
 (return status = 0)
1> sp_adduser frank
2> go
New user added.
 (return status = 0)
1> grant dbcc checkdb on all to frank
2> go

Now Frank can execute the dbcc checkdb command on each database in the server where he is a valid user:

% isql -Ufrank -Pfrankpassword -SSERVER
1> dbcc checkdb (tempdb)
2> go
Checking tempdb: Logical pagesize is 2048 bytes
Checking sysobjects: Logical pagesize is 2048 bytes
...
The total number of data pages in this table is 1. DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

NoteYou cannot grant or revoke dbcc commands to public or groups.

Example 26

Grants Alex permission to use the dbcc tune command on pubs2. This example returns an error because you cannot grant server-level dbcc commands at the database level:

grant dbcc tune on pubs2 to alex
Msg 4626, Level 16, State 1:
Line 1:
DBCC command 'tune' cannot be assigned at database-level.

Example 27

Grants dbcc tune on the master database to Alex. This returns an error because even if the current database is master, you cannot grant a server-level command at the database level. The on database parameter shows the intention to restrict the access to the current database scope, and this is not possible for server-level commands:

grant dbcc tune on master to alex
Msg 4626, Level 16, State 1:
Line 1:
DBCC command 'tune' cannot be assigned at the database-level.

Example 28

Grants dbcc tune to Alex. This returns an error because server-level commands require that master be the current database:

use pubs2
grant dbcc tune to alex
Msg 4627, Level 16, State 1:
Line 1:
The user must be in the master database to GRANT/REVOKE this command.

Example 29

Grants dbcc checkalloc on the pubs2 database to “nonuser.” This returns an error because a user must be a valid user in the database to be granted database-level access:

grant dbcc checkalloc on pubs2 to nonuser
Msg 11105, Level 11, State 1:
Line 1:
No such user/role 'nonuser' exists.

Example 30

Grants dbcc tune on all to Alex:

grant dbcc tune on all to alex

The on all parameter is ignored because server-wide commands are always granted in the master database, and any access granted in the master database is granted for any database by default. Although the on all clause is not designed for server-wide commands, its use does not cause any errors because it is a default behavior.

Example 31

Grants dbcc checkalloc on all and dbcc checkdb on pubs2 to Alex. Although several commands can be granted under the same statement, they must all affect the same database, so you must be in master if one of them is on all:

grant dbcc checkalloc on all,
dbcc checkdb on pubs2 to alex
Msg 4627, Level 16, State 1:
Line 1:
The user must be in the master database in order to grant/revoke server-wide DBCC access.

Example 32

Erroneously applies grant dbcc and revoke dbcc to groups or public:

1> grant dbcc tablealloc on pubs2 to public
Msg 4629, Level 16, State 1:
Line 1:
GRANT/REVOKE DBCC does not apply to groups or PUBLIC.
1> sp_addgroup gr
New group added.
 (return status = 0)
1> grant dbcc tablealloc on pubs2 to gr
Msg 4629, Level 16, State 1:
Line 1:
GRANT/REVOKE DBCC does not apply to groups or PUBLIC.

Example 33

Granting a database-level command at the database level has no effect if a server-wide permission exists:

1> grant dbcc checkalloc on all to alex
1> use pubs2
1> grant dbcc checkalloc on pubs2, dbcc tablealloc on pubs2 to alex
1> exec sp_helprotect
grantor   grantee   type   action   object    column            grantable
-------   -------   ----   ------   -------   -------           ---------
dbo       alex     Grant   DBCC    DBCC      dbcc tablealloc   FALSE
 (return status = 0)

Example 34

Only the System Administrator can grant the privilege:

set role sa_role off
grant dbcc tablealloc on all to alex
Msg 10353, Level 14, State 1:
Line 1:
You must have the following roles to execute this command/procedure: 'sa_role'. Please contact a user with the appropriate role for help.

Example 35

Granting a dbcc traceon results in an error message because dbcc traceon is not a grantable command:

grant dbcc traceon to joe
go
Msg 4607, Level 16, State 2:
Line 12:
Privilege DBCC traceon may not be GRANTed or REVOKEd.

See Table 1-25 for a list of commands you can grant.

Example 36

The col_name function displays only the dbcc commands that can be granted, and returns the string dbcc internal for all the dbcc commands that cannot be granted.

1> declare @a int
2> select @a=1
3> while (@a<200)
4> begin
5> insert #t values (@a, col_name (-317, @a))
6> select @a=@a+1
7> end
8> select dbcc_id=a, dbcc_command=b from #t where b!="dbcc internal"
 dbcc_id  dbcc_command
-------- ------------------------------ 

        1 dbcc catalogcheck 
        2 dbcc checktable
        3 dbcc checkalloc
        4 dbcc checkdb
        5 dbcc checkindex
        6 dbcc reindex
        9 dbcc fix_text
       11 dbcc tablealloc
       12 dbcc indexalloc
       13 dbcc textalloc
       18 dbcc tune
       37 dbcc checkstorage
       40 dbcc checkverify

Example 37

You cannot use the grant dbcc command using the grant option:

grant dbcc tune to alex with grant option
Msg 156, Level 15, State 1:
Line 1:
Incorrect syntax near the keyword 'with'.

Example 38

Runs checkverify on the table tab, with exclusion list disabled, in the database my_db:

dbcc checkverify(my_db, tab)

Example 39

Runs dbcc checkverify on table tab, in database my_db, with the exclusion list enabled:

dbcc checkverify (my_db, tab, 0)

Example 40

Runs dbcc checkverify on table tab, in database my_db, with the exclusion list disabled, enter:

dbcc checkverify (my_db, tab, 1)

Example 41

Allows user “harry” to use truncate table and updates statistics on the authors table:

grant truncate table on authors to harry
grant update statistics on authors to harry

Example 42

Allows user “billy” to use the delete statistics command on the authors table:

grant delete statistics on authors to billy

Example 43

Grants truncate table, update, and delete statistics privileges to all users with the oper_role (if users “billy” and “harry” possess the oper_role, they can now execute these commands on authors):

grant truncate table on authors to oper_role
grant update statistics on authors to oper_role
grant delete statistics on authors to oper_role

Example 44

Implicitly grants permissions for truncate table, delete statistics, and update statistics through a stored procedure. For example, assuming “billy” owns the authors table, he can execute the following to grant “harry” privileges to run truncate table and update statistics on authors:

create procedure sproc1
as
truncate table authors
update statistics authors
go
grant execute on sproc1 to harry
go

You can also implicitly grant permissions at the column level for update statistics and delete statistics through stored procedures.

Example 45

Grants set proxy to user “joe” but restricts him from switching identities to any user with the sa, sso, or admin roles (however, if he already has these roles, he can set proxy for any user with these roles):

grant set proxy to joe
restrict role sa_role, sso_role, admin_role

When “joe” tries to switch his identity to a user with admin_role (in this example, Our_admin_role), the command fails unless he already has admin_role:

set proxy Our_admin_role
Msg 10368, Level 14, State 1:
Server 's', Line 2:Set session authorization permission denied because the target login has a role that you do not have and you have been restricted from using.

After “joe” is granted the admin_role and retries the command, it succeeds:

grant role admin_role to joe
set proxy Our_admin_role

Example 46

Restricts “joe” from being granted any new roles when switching identities:

grant set proxy to joe
restrict role all

“joe” can set proxy only to those users who have the same (or lesser) roles than he has.

Example 47

Restricts Joe from acquiring any new system roles when using set proxy:

grant set proxy to joe
restrict role system

set proxy fails if the target login has system roles that Joe lacks.

Usage


grant all object creation permissions


grant with grant option rules


Granting permission to roles


Users and user groups


grant dbcc command options

Table 1-25 lists the valid grant dbcc commands.

Table 1-25: dbcc command options

Command name

Description

checkalloc

Checks the specified database to make sure all of its pages are correctly allocated, and that there are no unused allocated pages.

checkcatalog

Checks for consistency in and between system tables.

checkdb

Runs the same checks as checktable, but on each table in the specified database, including syslogs.

checkindex

Checks the specified index to make sure that:

  • Index and data pages are correctly linked.

  • Indexes are correctly sorted.

  • All pointers are consistent.

  • Data information on each page is reasonable.

  • Page offsets are reasonable.

checkstorage

Checks the specified database for:

  • Allocation

  • OAM page entries

  • Page consistency

  • Text-valued columns

  • Allocation of text-valued columns

  • Text-column chains

checktable

Checks the specified table to make sure that:

  • Index and data pages are correctly linked.

  • Indexes are correctly sorted.

  • All pointers are consistent.

  • Data information on each page is reasonable.

  • Page offsets are reasonable.

checkverify

Verifies the results of the most recent run of dbcc checkstorage for the specified database.

fix_text

Upgrades text values after any Adaptive Server character set is converted to a new multibyte character set.

indexalloc

Checks the specified index to make sure all pages are correctly allocated, and that there are no unused allocated pages.

reindex

Checks the integrity of indexes on user tables by running a fast version of dbcc checktable.

tablealloc

Checks the specified table to make sure that all pages are correctly allocated, and that there are no unused allocated pages.

textalloc

Checks for a violation of the format of the root page of a text, unitext, or image index.

tune

Enables or disables tuning flags for special performance situations.

All of the options in Table 1-25 are database-level commands except for tune, which is a server-level command.

See Chapter 25, “Checking Database Consistency” in the System Administration Guide for more information on these dbcc commands.


on all | database parameter and server-level commands

The on database parameter specifies the database on which to invoke the database-level grant dbcc command. Because on master grants the ability to use dbcc commands on all databases, on master is the same as on all. You must be in the master database to use either the on all or on master parameters.

Neither the on database nor on all parameters work when invoking a server-level grant dbcc command such as dbcc tune, because by doing so, you are forcing a server-level command to restrict itself to individual databases. For this reason, using the server-level grant dbcc tune on master command raises an error.


on all and guest

Before you grant dbcc permission for a database to a user, that user must first be a valid user in the database, and cannot be a “guest” user. However, if you grant dbcc through roles, the users can then execute that dbcc command in any database where they are a valid user, including the user “guest.”


granting default permissions on system tables

Table 1-26 lists the system tables you can grant and revoke the default permissions for when you issue the command from any database.

Table 1-26: System tables with grantable permissions issued from any database

sysalternates

sysjars

sysqueryplans

systypes

sysattributes

syskeys

sysreferences

sysusermessages

syscolumns

syslogs

sysroles

sysusers

syscomments

sysobjects

syssegments

sysxtypes

sysconstraints

syspartitions

sysstatistics

sysdepends

sysprocedures

systabstats

sysindexes

sysprotects

systhresholds

The command also makes the following changes:

Table 1-27 lists the system tables you can grant and revoke the default permissions for when you issue the command from the master database.

Table 1-27: System tables with grandable permissions issued from the master database

sysdatabases

sysusages

systimeranges

sysdevices

sysconfigures

sysresourcelimits

syslocks

syscurconfigs

syslogins

sysmessages

syslanguages

sysremotelogins

sysprocesses

syscharsets

syssessions

systransactions

sysservers

The command also makes the following changes:


Granting permissions for update statistics, delete statistics, and truncate table

Adaptive Server allows you to grant permissions for users, roles, and groups for the update statistics, delete statistics, and truncate table commands. Table owners can also provide permissions through an implicit grant by adding update statistics, delete statistics, and truncate table to a stored procedure and then granting execute permissions on that procedure to a user or role.

You cannot grant permissions for update statistics at the column level. You must have the sso_role to run update statistics or delete statistics on sysroles, syssrvroles, and sysloginroles security tables.

By default, users with the sa_role have permission to run update statistics and delete statistics on system tables other than sysroles, syssrvroles and sysloginroles, and can transfer this privilege to other users.

You can also issue grant all to grant permissions on update statistics, delete statistics, and truncate table.

NoteOnce you grant permission to execute update statistics to a user, they also have permission to execute variations of this command, such as update all statistics, update partition statistics, update index statistics, update statistics table, and so on. For example, the following grants “billy” permission to run all variations of update statistics on the authors table:

grant update statistics on authors to billy

If you revoke a user’s permission to execute update statistics, you also revoke their ability to execute the variations of this command.

You cannot grant variants of update statistics (for example, update index statistics) separately. That is, you cannot issue:

grant update all statistics to harry

However, you can write stored procedures that control who executes these commands. For example, the following grants “billy” execute permission for update index statistics on the authors table:

create proc sp_ups as
update index statistics on authors
go
revoke update statistics on authors from billy
go
grant execute on sp_ups to billy

You cannot grant and revoke delete statistics permissions at the column level.

Although Adaptive Server audits truncate table as a global, miscellaneous audit, it does not audit update statistics. To retain clear audit trails for both truncate table and update statistics, Sybase recommends that you include both commands in a stored procedure to which you grant users execute permission, as described above.

The command fails and generates an error message if a user issues update statistics, delete statistics or truncate table and they:


Granting proxies and session authorizations


Granting permissions in a shared-disk cluster

grant fails if you attempt to grant permissions to user-defined roles in a local temporary database.

Standards

ANSI SQL – Compliance level: Entry-level compliant. grant dbcc is also 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. grant dbcc is also Transact-SQL extension.

Permissions

Command execution Only System Administrators can grant create database permission, and only from the master database. Only System Security Officers can grant create trigger permission. Only system security officers can grant create trigger and create encryption key permissions.

Database consistency checking Only System Administrators can run grant dbcc commands. Database Owners cannot run grant dbcc.

Database object access grant 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 SSO 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.

Roles You can grant roles only from the master database. Only System Security Officers can grant sso_role, oper_role or a user-defined role to a user or a role. Only System Administrators can grant sa_role to a user or a role. Only users who have both sa_role and sso_role can grant a role that includes sa_role.

System tables Database Owners can grant default permissions on system tables.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event

Audit option

Command or access audited

Information in extrainfo

40

grant

grant

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

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

85

roles

create role, drop role, alter role, grant role, or revoke role

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

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

See also

Catalog stored procedures sp_column_privileges, sp_table_privileges

Commands create role, revoke, setuser, set

Functions proc_role, show_role

System procedures sp_addgroup, sp_adduser, sp_changedbowner, sp_changegroup, sp_dropgroup, sp_dropuser, sp_helpgroup, sp_helprotect, sp_helpuser, sp_role