Grantable system privileges

Privileges management

manage security permissions

Granting and revoking security privileges

manage server permissions

Granting and revoking nonsecurity server-wide privileges not related to security.

Audit management

manage auditing

Acting as the database owner of sybsecurity

Executing these commands:

• create database sybsecurity

• alter database sybsecurity

• drop database sybsecurity

• set switch (7601)

• truncate table (audit table)

Executing these system store procedures:

• sp_addaudittable

• sp_audit

• sp_displayaudit

Login and role management

allow exceptional login

Granting login to the server when:

• Server maximum connection limit exceeded

• Master database is in restore mode

• Server is in shutdown

• Recovery is in progress (during server restart)

change password

Executing this command:

• alter login ... change password

Executing these system store procedures:

• sp_password

• sp_locklogin (unlock any login account which was locked because the user exceeded the limit of maximum failed logins)

manage any login

Executing these commands:

• create login

• alter login

• drop login

Executing these system procedures:

• sp_addlogin

• sp_autoconnect

• sp_defaultdb

• sp_displaylogin

• sp_droplogin

• sp_grantlogin (Windows only)

• sp_helpmaplogin

• sp_logininfo (Windows only)

• sp_locklogin

• sp_maplogin

• sp_modifylogin

• sp_revokelogin (Windows only)

Executing this function:

• valid_user

manage any login profile

Executing these commands:

• create login profile

• alter login profile

• drop login profile

Executing this system procedure:

• sp_securityprofile

manage any remote login

Executing these system procedures:

• sp_addexternlogin

• sp_addremotelogin

• sp_dropexternlogin

• sp_dropremotelogin

• sp_dropserver

• sp_remoteoption

manage roles

Executing these commands:

• create role

• alter role

• drop role

• grant role

• revoke role

Executing these system procedures:

• sp_displayroles

• sp_grantlogin

• sp_logininfo

• sp_revokelogin

• sp_role

Database Management

checkpoint

Executing the checkpoint command for a specified database

checkpoint any database

Executing the checkpoint command for any database

create database*

Executing the create database command

dump any database

Executing these commands for any database:

• dump database

• dump transaction

dump database

Executing these commands for a specified database:

• dump database

• dump transaction

load any database

Executing these commands for any database:

• load database

• load transaction

load database

Executing these commands for a specified database:

• load database

• load transaction

manage any database

Performing database maintenance operations on any database

Executing these commands:

• install jar

• remove jar class

Executing these system procedures:

• sp_addobjectdef

• sp_addsegment

• sp_addthreshold

• sp_checksource

• sp_dbextend 'simulate'

• sp_dbextend 'execute'

• sp_dbextend 'clear','threshold'

• sp_dbextend 'check'

• sp_dbextend 'set','threshold'

• sp_dbextend 'modify', 'database'

• sp_dbextend 'set','database'

• sp_dbextend 'set', 'threshold'

• sp_dropobjectdef

• sp_dropsegment

• sp_dropthreshold

• sp_droptype

• sp_extendsegment

• sp_hidetext

• sp_modifthreshold

• sp_placeobject

• sp_procxmode

• sp_rename

• sp_rebuild_text

• sp_spaceusage (for some parameters)

manage any database (continued)

Executing these dbcc commands:

• dbcc dbrepair(remap)

• dbcc dbrepair(newthreshold)

• dbcc dbrepair(findstranded)

• dbcc dbrepair(fixlogfreespace)

• dbr_remap

• dbcc dbrepair(ltmignor)

• dbcc dbrepair(updusg_anchors)

• dbcc dbrepair(auinit)

• dbcc dbrepair(dmap_unlock)

• dbcc rebuild_text

• dbcc refreshids (placeobject)

• dbcc refreshpdes

• dbcc update_tmode

• dbcc upgrade_obj

Executing these functions:

• derived_stat

• identity_burn_max

mount any database

Executing the mount database command for any database

online any database

Executing the online database command for any database

online database

Executing the online database command for a specified database

own any database

Acting as database owner for the specified database

See own database for a list of operations the privilege is authorized to perform.

own database

Acting as database owner for any database except sybsecurity

Executing these commands:

• alter database

• drop database

• grant (default)

• revoke (default)

• checkpoint

• dump database

• dump transaction

• load database

• load transaction

• online database

• use database

own database (continued)

Executing these system procedures:

• sp_addmessage

• sp_altermessage

• sp_dboption

• sp_dbremap

• sp_dropmessage

• sp_fixindex

• sp_forceonline_db

• sp_forceonline_page

• sp_helptext

• sp_logdevice

• sp_post_xpload

• sp_renamedb

• sp_setsuspect_granularity

• sp_tempdb_markdrop

• sp_version

• xp_cmdshell

• xp_deletemail

• xp_enumgroups

• xp_findnextmsg

• xp_logevent

• xp_readmail

• xp_sendmail

• xp_startmail

• xp_stopmail

Executing these dbcc commands:

• dbcc addtempdb

• dbcc dbrepair

• dbcc reindex

quiesce any database

Executing the quiesce database command for any database

unmount any database

Executing the unmount database command for any database

Server Maintenance

manage any thread pool

Executing these commands:

• create thread pool

• alter thread pool

• drop thread pool

manage cluster

Managing cluster-related configurations and operations

Executing the shutdown cluster command (requires shutdown privilege)

Executing these system procedures:

• sp_addserver

• sp_cluster

• sp_clusterlockusage

• sp_dropserver

• sp_serveroption

• sp_tempdb_markdrop

Executing these dbcc commands:

• dbcc quorum

• dbcc set_scope_in_cluster

manage disk

Executing these commands:

• disk init

• disk refit

• disk reinit

• disk mirror

• disk unmirror

• disk remirror

Executing these system procedures:

• sp_addumpdevice

• sp_diskdefault

• sp_deviceattr

• sp_dropdevice

• sp_refit_admin

• sp_dbextend (required by some options)

manage security configuration

Enable or disable security related configurations

Executing these system procedures:

• sp_configure (to set security-related configuration options)

• sp_encryption

• sp_logintrigger

• sp_ldapadmin

• sp_passwordpolicy

• sp_ssladmin

manage server

Manage server maintenance operations

Executing these system procedures:

• sp_addlanguage

• sp_addserver (current security system officer)

• sp_clearstats

• sp_countmetadata

• sp_dbrecovery_order

• sp_displaylogin

• sp_displayroles

• sp_droplanguage

• sp_dropserver (current security system officer)

• sp_engine

• sp_errorlog

• sp_extengine

• sp_helpapptrace

• sp_metrics

• sp_monitorconfig

• sp_object_stats

• sp_processmail (Windows only)

• sp_reportstats

• sp_serveroption

• sp_setlangalias

• sp_tempdb

Executing these dbcc commands:

• dbcc complete_xact

• dbcc engine

• dbcc forget_xact

• dbcc traceflags

Executing these functions:

• pssinfo

• valid_user

manage server configuration

Enable or disable server configurations not related to security

Executing these system procedures:

• sp_configure (set security related configuration options)

• sp_displaylevel

• sp_jreconfig

• sp_lmconfig

• sp_pciconfig

shutdown

Shutting down the:

• Server

• Cluster (also requires manage cluster privilege)

• Instance

• Backup Server

Execute the shutdown command.

dbcc

dbcc checkalloc any database

Executing dbcc checkalloc in any database

dbcc checkcatalog any database

Executing dbcc checkcatalog in any database

dbcc checkdb any database

Executing dbcc checkdb in any database

dbcc check index any database

Executing dbcc checkindex in any database

dbcc checkstorage any database

Executing dbcc checkstorage in any database

dbcc checktable any database

Executing dbcc checktable in any database

dbcc checkverify any database

Executing dbcc checkverify in any database

dbcc fix_text any database

Executing dbcc fix_text in any database

dbcc indexalloc any database

Executing dbcc indexalloc in any database

dbcc reindex any database

Executing dbcc reindex in any database

dbcc tablealloc any database

Executing dbcc tablealloc in any database

dbcc textalloc any database

Executing dbcc textalloc in any database

dbcc tune

Executing dbcc tune

Application Management

manage any execution class

Executing these system procedures:

• sp_addengine

• sp_addexeclass

• sp_bindexeclass

• sp_clearpsexe

• sp_dropengine

• sp_dropexeclass

• sp_setpsexe

• sp_unbindexeclass

manage any ESP

Executing these system procedures:

• sp_addextendedproc

• sp_dropextendedproc

• sp_freedll

• sp_helpextendedproc

manage data cache

Executing these system procedures:

• sp_bindcache

• sp_cacheconfig

• sp_cachestrategy

• sp_logiosize

• sp_poolconfig

• sp_unbindcache

• sp_unbindcache_all

manage dump configuration

Managing dump configuration for a backup server

Executing these commands:

• dump configuration

Executing these system procedures:

• sp_config dump

• sp_dump history

manage lock promotion threshold

Executing these system procedures:

• sp_dropglockpromote

• sp_droprowlockpromote

• sp_setpglockpromote

• sp_setrowlockpromote

monitor qp performance

Monitoring query processing performance

Executing these commands:

• set switch (3604, 3605)

• set tracefile

• set plan for plan_list

• set option <optimizer_show_option> {value | on |off}

Executing these system procedures:

• sp_cmp_all_qplans

• sp_cmp_qplans

• sp_find_qplans

• sp_flush_query_tuning

• sp_flushmetrics

• sp_flushstats

• sp_metrics (for 'filter', 'show', 'help')

• sp_showplan

Executing these dbcc commands:

• dbcc traceoff(3604, 3605)

• dbcc traceon(3604, 3605)

• dbcc nodetraceoff(3604, 3605)

• dbcc nodetraceon(3604, 3605)

manage resource limit

Executing these system procedures:

• sp_add_resource_limit

• sp_add_time_range

• sp_drop_resource_limit

• sp_drop_time_range

• sp_help_resource_limit

• sp_modify_resource_limit

• sp_modify_time_range

Others

connect*

Connecting to any server using the connect command

kill

Killing processes owned by the privilege holder

kill any process

Killing any process owned by any user

map external file

Mapping a proxy table to a directory or file on a remote server

monitor server replication

Displaying replication status

Executing these system procedures:

• sp_config_rep_agent (no configure value specified, with or without database name specified)

• sp_help_rep_agent (with or without the database name specified)

set proxy

Executing set proxy to change the identity to another user

set tracing*

Executing these commands:

• set tracefile (for your own session)

• set plan for <plan_list> on|off

• set option <optimizer_show_option>on|off

Executing these dbcc commands:

• dbcc traceoff(3604, 3605)

• dbcc traceon(3604, 3605)

• dbcc nodetraceoff(3604, 3605)

• dbcc nodetraceon(3604, 3605)

set tracing any process

Executing these commands:

• set tracefile (for any session)

• set plan for <plan_list> on|off

• set option <optimizer_show_option> on|off

Executing these dbcc commands:

• dbcc traceoff(3604, 3605)

• dbcc traceon(3604, 3605)

• dbcc nodetraceoff(3604, 3605)

• dbcc nodetraceon(3604, 3605)

set switch

Enabling or disabling any trace flag

Executing these commands:

• set switch

• show switch

Executing these dbcc commands:

• dbcc traceon

• dbcc traceoff

• dbcc nodetraceon

• dbcc nodetraceoff

Executing this stored procedure:

• sp_dbextend 'trace'

show switch

Displays traceflags that are on

Execute the show switch command

use any database

Accessing any database when the privilege holder is not a valid user of the database and there is no “guest” account in the database

Execute the use database command

use database

Accessing the specified database when the privilege holder is not a valid user of the database and there is no guest account in the database

Execute the use database command

Permission management

manage any object permission

Granting and revoking object permissions

manage database permissions

Granting and revoking database privileges

Manage user

manage any user

Executing these system procedures:

• sp_addalias

• sp_addgroup

• sp_adduser

• sp_changegroup

• sp_dropalias

• sp_dropgroup

• sp_dropuser

Set user

setuser

Impersonating another user

Replication Management

manage replication

Managing replication settings in a database

Executing these commands:

• set replication

• set repmode

• set repthreshold

Executing these system procedures:

• sp_config_rep_agent (with database name specified)

• sp_help_rep_agent (with database name specified)

• sp_replication_path

• sp_reptostandby

• sp_setrepcol

• sp_setrepdb

• sp_setrepdbmode

• sp_setrepdefmode

• sp_setreplicate

• sp_setrepproc

• sp_setreptable

• sp_start_rep_agent

• sp_stop_rep_agent

Executing these dbcc commands:

• dbcc gettrunc

• dbcc settrunc

Maintains database

manage database

Performing database maintenance operations without accessing dbo-owned data

Executing these commands:

• install jar

• remove jar class

Executing these system procedures:

• sp_addobjectdef

• sp_addsegment

• sp_addthreshold

• sp_checksource

• sp_dropobjectdef

• sp_dropsegment

• sp_dropthreshold

• sp_droptype

• sp_extendsegment

• sp_hidetext

• sp_merge_dup_inline_default

• sp_modifthreshold

• sp_placeobject

• sp_procxmode

• sp_rebuild_text

• sp_spaceusage (for some parameters)

manage database (continued)

Executing these dbcc commands:

• dbcc dbrepair(remap)

• dbcc dbrepair(newthreshold)

• dbcc dbrepair(findstranded)

• dbcc dbrepair(fixlogfreespace)

• dbcc dbr_remap

• dbcc dbrepair(ltmignor)

• dbcc dbrepair(updusg_anchors)

• dbcc dbrepair(auinit)

• dbcc dbrepair(dmap_unlock)

• dbcc rebuild_text

• dbcc refreshids (placeobject)

• dbcc refreshpdes

• dbcc update_tmode

• dbcc upgrade_obj

Executing these built-in functions:

• derived_stat

• identity_burn_max

• lct_admin

• next_identity

Manage query plan

manage abstract plans

Executing these system procedures:

• sp_add_qpgroup

• sp_cmp_all_qplans

• sp_cmp_qplans

• sp_copy_all_qplans

• sp_drop_all_qplans

• sp_drop_qpgroup

• sp_drop_qplan

• sp_export_qpgroup

• sp_find_qplan

• sp_help_qpgroup

• sp_help_qplan

• sp_import_qpgroup

• sp_rename_qpgroup

• sp_set_qplan

dbcc

dbcc checkalloc*

Executing dbcc checkalloc in the database

dbcc checkcatalog*

Executing dbcc checkcatalog in the database

dbcc checkdb*

Executing dbcc checkdb in the database

dbcc checkindex*

Executing dbcc checkindex in the database

dbcc checkstorage*

Executing dbcc checkstorage in the database

dbcc checktable*

Executing dbcc checktable in the database

dbcc checkverify*

Executing dbcc checkverify in the database

dbcc fix_text*

Executing dbcc fix_text in the database

dbcc indexalloc*

Executing dbcc indexalloc in the database

dbcc reindex*

Executing dbcc reindex in the database

dbcc tablealloc*

Executing dbcc tablealloc in the database

dbcc textalloc*

Executing dbcc textalloc in the database

manage checkstorage

Managing dbcc checkstorage-related settings on the database (specified with the procedures in which the privilege is granted)

Executing these dbcc stored procedures:

• sp_dbcc_deletedb

• sp_dbcc_deletehistory

• sp_dbcc_evaluatedb

• sp_dbcc_exclusions

• sp_dbcc_patch_finishtime

• sp_dbcc_updateconfig

report checkstorage

Executing dbcc procedures to generate reports about dbcc checkstorage results on the database (specified with the procedures in which the privilege is granted)

Executing these dbcc stored procedures:

• sp_dbcc_configreport

• sp_dbcc_differentialreport

• sp_dbcc_faultreport

• sp_dbcc_fullreport

• sp_dbcc_recommendations

• sp_dbcc_statisticsreport

• sp_dbcc_summaryreport

System Catalog

select any audit table

Selecting any audit table in sybsecurity (available only in sybsecurity database)

select any system catalog

Selecting all columns from any system table in the current database

truncate any audit table

Truncating any audit table in sybsecurity (available only in the sybsecurity database)

update any security catalog

Updating, inserting, and deleting these security-related system catalogs, which are restricted from direct update:

• master.dbo.syslogins

• master.dbo.syssrvroles

• master.dbo.sysloginroles

• db.dbo.sysroles

• db.dbo.sysprotects

Manage objects

alter any object owner

Altering ownership for any object in the database

Execute the alter ... modify owner command.

create any object

Creating any of these objects owned by anyone:

• tables

• views

• procedures

• functions

• defaults

• rules

• indexes

• triggers

Executing these commands:

• create table

• create view

• create procedure

• create function

• create rule

• create default

• create trigger

• create index

drop any object

Dropping any of these objects owned by anyone:

• tables

• views

• procedures

• functions

• defaults

• rules

• indexes

• triggers

Executing these commands:

• drop default

• drop function

• drop index

• drop procedure

• drop rule

• drop table

• drop trigger

• drop view

Manage encryption

create encryption key*

Creating encryption keys in the database

manage any encryption key

Creating, altering , and dropping column encryption keys, master keys, and service keys owned by anyone

Executing these commands:

• create encryption key

• alter encryption key

• drop encryption key

Executing sp_encryption

manage column encryption key

Creating, altering, and dropping column encryption keys

manage master key

Creating, altering, and dropping master keys

manage service key

Creating, altering, and dropping service keys

Defaults

create default*

Creating self-owned default

Execute the create default command

create any default

Creating defaults owned by anyone

Execute the create default command

drop any default

Dropping defaults owned by anyone

Execute the drop default command

Functions

create function*

Creating self-owned user-defined function

Executing these commands:

• create function

• create function (SQLJ)

create any function

Creating functions owned by anyone

Executing these commands:

• create function

• create function (SQLJ)

drop any function

Dropping functions owned by anyone

Executing these commands:

• drop function

• drop function (SQLJ)

execute any function

Running user-defined functions owned by anyone

Execute the execute command

Indexes

create any index

Creating indexes on tables owned by anyone

Execute the create index command

Procedures

create procedure

Creating self-owned procedures

Execute the create procedure command

create any procedure

Creating procedures owned by anyone

Execute the create procedure command

execute any procedure

Execute procedures owned by anyone

Execute the execute command.

drop any procedure

Dropping procedures owned by anyone

Execute the drop procedure command

Rules

create rule*

Creating self-owned rule

Execute the create rule command

create any rule

Creating rule owned by anyone

Execute the create rule command

drop any rule

Dropping rules owned by anyone

Execute the drop rule command.

Tables

alter any table

Altering user tables owned by anyone

Execute the alter table command.

create any table

Creating user tables owned by anyone

Execute the create table command.

create table*

Creating self-owned user tables

Execute the create table command

decrypt any table

Decrypting any encrypted table

delete any table

Delete rows of user tables owned by anyone

Executing these commands:

• delete table

• lock table

drop any table

Dropping user tables owned by anyone

Execute the drop table command.

identity_insert any table

Enabling or disabling identity_update on any user table

Execute the set identity_insert command

identity_update any table

Enabling or disabling identity_insert on any user table

Execute the set identity_update command

insert any table

Inserting user tables owned by anyone

Executing these commands:

• insert

• lock table

manage any statistics

Update or delete statistics on any table owned by anyone

Executing these commands:

• delete statistics

• update statistics

Executing sp_modifystats

references any table

Referencing user tables owned by anyone

reorg any table

Reorganizing user tables owned by anyone

Execute the reorg command

select any table

Selecting user tables owned by anyone

Execute these commands:

• select

• lock table (for share lock)

• readtext

transfer any table

Transferring data to or from user tables owned by anyone

Execute the transfer table command

truncate any table

Truncating user tables owned by anyone

Execute the truncate table command

update any table

Updating user tables owned by anyone

Execute these commands:

• update

• lock table

• writetext

Trigger

create trigger*

Creating self-owned trigger.

Execute the create trigger command.

create any trigger

Creating triggers owned by anyone

Execute the create trigger command

drop any trigger

Dropping triggers owned by anyone

Execute the drop trigger command

Views

create view*

Creating self-owned view

Execute the create view command

create any view

Creating views owned by anyone

Execute the create view command.

drop any view

Dropping views owned by anyone

Execute the drop view command.