Assigns permissions to users or to user-defined roles. Assigns roles to users or system or user-defined roles.
To grant permission to access database objects:
grant {all [privileges]| permission_list}
on { table_name [(column_list)]
| view_name[(column_list)]
| stored_procedure_name}
to {public | name_list | role_name}
[with grant option]
To grant permission to select built-in functions:
grant select
on [builtin] built-in
to { name_list | role_name }
To grant permission to execute certain commands:
grant {all [privileges] | command_list}
to {public | name_list | role_name}
To grant a role to a user or a role:
grant {role role_granted [, role_granted ...]}
to grantee [, grantee...]
To grant and revoke access on certain dbcc commands:
grant dbcc {dbcc_command [on {all | database }]
[, dbcc_command [on {all | database }], ...]}
to { user_list | role_list }
when used to assign permission to access database objects (the first syntax format), all specifies that all permissions 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.
Only a System Administrator or the Database Owner can assign permission to create database objects (the second 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, Adaptive Server grants all create permissions except create database, and prints an informational message.
Specifying all does not include permission to execute set proxy or set session authorization.
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 |
View |
select, insert, delete, update |
Column |
select, update, references Column names can be specified in either permission_list or column_list (see Example 2). |
Stored procedure |
execute |
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.
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, and set session authorization.
create database permission can be granted only by a System Administrator, and only from within the master database.
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.
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.
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.
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.
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.
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.
is a list of users’ database names and/or group names, separated by commas.
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.
grants a role to a user or to a system or user-defined role.
is the name of a system or user-defined role that the System Security Officer is granting to a user or a role.
is the name of a system role, user-defined role, or a user, to whom you are granting a role.
is the name of a system or user-defined role to which you are granting the permission.
is the name of the dbcc command you are granting. It cannot be a variable. Table 1-27 lists the valid grant dbcc commands.
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.
is a list of users to whom you are granting the permission, and cannot be a variable.
is a list of the name of system or user-defined roles to whom you are granting the permission, and cannot be a variable.
You cannot grant or revoke dbcc commands
to public or groups.
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
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
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
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
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
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
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
Grants complete access permissions on the titles table to all users:
grant all on titles to public
Grants all object creation permissions 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
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
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
Grants permission to execute the new_sproc stored procedure to all System Security Officers:
grant execute on new_sproc to sso_role
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
Grants the role “specialist”, with all its permissions and privileges, to the role “doctor”:
grant role specialist_role to doctor_role
Grants the role “doctor” to Mary:
grant role doctor_role to mary
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.
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
The System Administrator must be in the target database—in
this case pubs2—and Walter must be
a valid user in this target database.
Walter 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.
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.
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.
You 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
Gives Frank, a valid user in the master database, the ability to execute dbcc 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.
You cannot grant or revoke dbcc commands
to public or groups.
Grants Alex permission to use the dbcc tune command on pubs2. This example returns an error because server-level dbcc commands cannot be granted 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.
Grants dbcc tune on the master database to Alex. This returns an error because even if the current database is master, a server-level command cannot be granted 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.
Grants dbcc tune to Alex. This returns an error because server-level commands require that master be the current database:
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.
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.
Grants dbcc tune on all to Alex. The on all parameter is ignored because any access granted in the master database is granted for any database by default. The command however, does not display any error:
grant dbcc tune on all to alex
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.
You cannot apply grant and revoke 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.
You cannot grant a database-level command at the database level 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)
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 role(s) to execute this command/procedure: 'sa_role'. Please contact a user with the appropriate role for help.
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-27 for a list of commands you can grant.
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 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
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'.
You can substitute the word from for to in the grant syntax.
Table 1-26 summarizes default permissions on Transact-SQL commands in Adaptive Server. The user listed under the “Defaults to” heading is the lowest level of user that is automatically granted permission to execute a command. This user can grant or revoke the permission if it is transferable. Users at higher levels than the default are either automatically assigned permission or (in the case of Database Owners) can get permission by using the setuser command.
For example, the owner of a database does not automatically receive permission on objects owned by other users. A Database Owner can gain such permission by assuming the identity of the object owner with the setuser command, and then issuing the appropriate grant or revoke statement. System Administrators have permission to access all commands and objects at any time.
The Adaptive Server installation script assigns a set of permissions to the default group “public.” grant and revoke statements need not be written for these permissions.
Table 1-26 does not include the System Security Officer, who does not have any special permissions on commands and objects, but only on certain system procedures.
Statement |
Defaults to |
Can be granted/revoked |
||||||
|---|---|---|---|---|---|---|---|---|
System Admin |
Operator |
Database Owner |
Object owner |
Public |
Yes |
No |
N/A |
|
alter database |
X |
(1) |
||||||
alter role |
X |
|||||||
alter table |
X |
X |
||||||
begin transaction |
X |
X |
||||||
checkpoint |
X |
X |
||||||
commit |
X |
X |
||||||
connect to |
X |
|||||||
create database |
X |
X |
||||||
create default |
X |
X |
||||||
create index |
X |
X |
||||||
create procedure |
X |
X |
||||||
create role |
X |
|||||||
create rule |
X |
X |
||||||
create table |
X |
(2) |
X (2) |
|||||
create trigger |
X |
X |
||||||
create view |
X |
X |
||||||
Varies depending upon options. See dbcc in this manual. |
X |
|||||||
X (3) |
X |
|||||||
disk init |
X |
X |
||||||
disk mirror |
X |
|||||||
disk refit |
X |
|||||||
disk reinit |
X |
|||||||
disk remirror |
X |
|||||||
disk unmirror |
X |
X |
||||||
drop any object |
X |
X |
||||||
dump database |
X |
X |
X |
|||||
dump transaction |
X |
X |
X |
|||||
execute |
X (4) |
X |
||||||
grant on object |
X |
X |
||||||
grant command |
X |
X |
||||||
insert |
X (3) |
X |
||||||
kill |
X |
X |
||||||
load database |
X |
X |
X |
|||||
load transaction |
X |
X |
X |
|||||
X |
X |
|||||||
raiserror |
X |
X |
||||||
readtext |
X |
(5) |
||||||
revoke on object |
X |
X |
||||||
revoke command |
X |
X |
||||||
rollback |
X |
X |
||||||
save transaction |
X |
X |
||||||
select |
X (3) |
X |
||||||
set |
X |
X |
||||||
setuser |
X |
X |
||||||
shutdown |
X |
X |
||||||
truncate table |
X |
X |
||||||
update |
X (3) |
X |
||||||
update all statistics |
X |
X |
||||||
update partition statistics |
X |
X |
||||||
update statistics |
X |
X |
||||||
writetext |
X |
(6) |
||||||
|
|
|||||||
You can grant permissions only on objects in your current database.
Before 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 (see example 10). 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.
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. For more information, see the System Administration Guide.
Adaptive 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 Adaptive Server objects (such as tables), so no permissions can be applied against a cursor. When a user opens a cursor, Adaptive 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, Adaptive Server opens the cursor and allows the user to fetch row data through the cursor. Adaptive 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, Adaptive 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, Adaptive Server retains the original row and adds a new row for the revoke.
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.
Permission to issue the create trigger command is granted to users by default. 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.
You can get information about permissions with these system procedures:
sp_helprotect reports permissions information for a database object or a user.
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 for the current login session of Adaptive Server.
sp_displayroles displays all roles granted to another role, 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 parameter with grant dbcc.
When used with only user or group names (no object names), grant all assigns these permissions: create database, create default, create procedure, create rule, create table, and create view. create database permission can be granted only by a System Administrator and only from within the master database.
Only the Database Owner and a System Administrator can use the grant all syntax without an object name to grant create command permissions to users or groups. When the grant all command is used by the Database Owner, an informational message is printed, stating that only a System Administrator can grant create database permission. All other permissions noted above are granted.
All object owners can use grant all with an object name to grant permissions on their own objects. When used with a table or view name plus user or group names, grant all enables delete, insert, select, and update permissions on the table.
You cannot grant permissions with grant option to “public” or to a group or role.
In granting permissions, a System Administrator is treated as the object owner. If a System Administrator grants permission on another user’s object, the owner‘s name appears as the grantor in sysprotects and in sp_helprotect output.
Information for each grant is kept in the system table sysprotects with the following exceptions:
Adaptive Server displays an informational message if a specific permission is granted to a user more than once by the same grantor. Only the first grant is kept.
If two grants are exactly same except that one of them is granted with grant option, the grant with grant option is kept.
If two grant statements grant the same permissions on a particular table to a specific user, but the columns specified in the grants are different, Adaptive Server treats the grants as if they were one statement. For example, the following grant statements are equivalent:
grant select on titles(price, contract) to keiko grant select on titles(advance) to keiko
grant select on titles(price, contract, advance) to keiko
Granting permission to execute set proxy or set session authorization allows the grantee to impersonate another login in Adaptive Server. set proxy and set session authorization are identical with one exception: set session authorization follows the SQL standard, and set proxy is a Transact-SQL extension.
To grant set proxy or set session authorization permission, you must be a System Security Officer, and you must be in the master database.
The name you specify in the grant set proxy command must be a valid user in the database; that is, the name must be in the sysusers table in the database.
grant all does not include the set proxy or set session authorization permissions.
You can use the grant command to grant permissions to all users who have been granted a specified role. The role can be either a system role, like sso_role or sa_role, or a user-defined role. For a user-defined role, the System Security Officer must create the role with a create role command.
However, grant execute permission does not prevent users who do not have a specified role from being individually granted permission to execute a stored procedure. If you want to ensure, for example, that only System Security Officers can ever be granted permission to execute a stored procedure, use the proc_role system function within the stored procedure itself. It checks to see whether the invoking user has the correct role to execute the procedure. For more information, see proc_role.
Permissions that are granted to roles override permissions that are granted to users or groups. For example, say John has been granted the System Security Officer role, and sso_role has been granted permission on the sales table. If John’s individual permission on sales is revoked, he can still access sales because his role permissions override his individual permissions.
User groups allow you to grant or revoke permissions to more than one user with a single statement. Each user can be a member of only one group and is always a member of “public”.
The Database Owner or System Administrator can add new users with sp_adduser and create groups with sp_addgroup. To allow users with logins on Adaptive Server to use the database with limited privileges, you can add a “guest” user with sp_adduser and assign limited permissions to “guest”. All users with logins can access the database as “guest”.
To remove a user, use sp_dropuser. To remove a group, use sp_dropgroup.
To add a new user to a group other than “public,” use sp_adduser. To change an established user’s group, use sp_changegroup.
To display the members of a group, use sp_helpgroup.
When sp_changegroup is executed to change group membership, it clears the in-memory protection cache by executing:
grant all to null
so that the cache can be refreshed with updated information from the sysprotects table. To modify sysprotects directly, contact Sybase Technical Support.
Table 1-27 lists the valid grant dbcc commands.
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. |
checkstorage |
Checks the specified database for:
|
checktable |
Checks the specified table to make sure that:
|
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 or image index. |
tune |
Enables or disables tuning flags for special performance situations. |
All of the options in Table 1-27 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.
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 and 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.
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 twhere they are a valid user, including the user “guest.”
ANSI SQL – Compliance level: Entry-level compliant.
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.
Database object access grant permission for database objects defaults to object owners. An object owner can grant permission to other users on his or her own database objects.
Command execution Only a System Administrator can grant create database permission, and only from the master database. Only a System Security Officer can grant create trigger permission.
Proxy and session authorization Only a System Security Officer can grant set proxy or set session authorization, and only from the master database.
Roles You can grant roles only from the master database. Only a System Security Officer 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 a user who has both sa_role and sso_role can grant a role which includes sa_role.
Database consistency checking Only System Administrators can run grant dbcc commands. Database Owners cannot run grant dbcc.
Catalog stored procedures sp_column_privileges
Functions proc_role
System procedures sp_addgroup, sp_adduser, sp_changedbowner, sp_changegroup, sp_dropgroup, sp_dropuser, sp_helpgroup, sp_helprotect, sp_helpuser, sp_role
