Auditing options: types and requirements

adhoc

(user-specific)

all

all

Any

Allows users to use sp_addauditrecord

This example enables ad hoc user-defined auditing records:

sp_audit "adhoc", "all", "all", "on" 

all

(user-specific)

A login name or role

all

Any

All actions of a particular user or by users with a particular role active

This example turns auditing on for all actions in which the sa_role is active:

sp_audit "all", "sa_role", "all", "on"

alter

(database-specific)

all

Database to be audited

Any

alter database, alter role, alter table

This example turns auditing on for all executions of alter database and alter table in the master database:

sp_audit @option = "alter", @login_name = "all",
@object_name = "master", @setting = "on" 

bcp

(database-specific)

all

Database to be audited

Any

bcp in

This example returns the status of bcp auditing in the pubs2 database:

sp_audit "bcp", "all", "pubs2"

If you do not specify a value for setting, Adaptive Server returns the status of auditing for the option you specify)

bind

(database-specific)

all

Database to be audited

Any

sp_bindefault, sp_bindmsg, sp_bindrule

This example turns bind auditing off for the planning database:

sp_audit "bind", "all", "planning", "off"

cmdtext

(user-specific)

Login name of the user to be audited

all

Any

SQL text entered by a user.(Does not reflect whether or not the text in question passed permission checks or not. eventmod always has a value of 1.)

This example turns text auditing off for database owners:

sp_audit "cmdtext", "sa", "all", "off"

create

(database-specific)

all

Database to be audited

Any

create database, create table, create role, create procedure, create trigger, create rule, create default, sp_addmessage, create view, create index, create function

This example turns on auditing of successful object creations in the planning database:

sp_audit "create", "all", "planning", "pass"

The current status of auditing create database is not affected because you did not specify the master database.)

dbaccess

(database-specific)

all

Database to be audited

Any

Any access to the database from another database

This example audits all external accesses to the project database:

sp_audit "dbaccess", "all", "project", "on"

dbcc

(global)

all

all

Any

All dbcc commands that require permissions

This example audits all executions of the dbcc command:

sp_audit "dbcc", "all", "all", "on"

delete

(object-specific)

all

Name of the table or view to be audited, or default view or default table

The database of the table or view (except tempdb)

delete from a table, delete from a view

This example audits all delete actions for all future tables in the current database:

sp_audit "delete", "all", "default table", "on"

disk

(global)

all

all

Any

disk init, disk refit, disk reinit, disk mirror, disk unmirror, disk remirror, disk resize

This example audits all disk actions for the server:

sp_audit "disk", "all", "all", "on"

drop

(database-specific)

all

Database to be audited

Any

drop database, drop table, drop role, drop procedure, drop index, drop trigger, drop rule, drop default, sp_dropmessage, drop view, drop function

This example audits all drop commands in the financial database that fail permission checks:

sp_audit "drop", "all", "financial", "fail"

dump

(database-specific)

all

Database to be audited

Any

dump database, dump transaction

This example audits dump commands in the pubs2 database:

sp_audit "dump", "all", "pubs2", "on"

encryption_key

(database-specific)

all

Database to be audited

Any

alter encryption key

create encryption key

drop encryption key

sp_encryption

This example audits all the above commands in the pubs2 database:

sp_audit "encryption_key", "all", "pubs2", "on"

errors

(global)

all

all

Any

Fatal error, non-fatal error

This example audits errors throughout the server:

sp_audit "errors", "all", "all", "on"

errorlog

all

all

Any

sp_errorlog or the errorlog_admin function

This example audits attempts to "change log" to move to a new Adaptive Server error log file:

sp_audit "errorlog", "all", "all", "on"

exec_procedure

(object-specific)

all

Name of the procedure to be audited or default procedure

The database of the procedure (except tempdb)

execute

This example turns automatic auditing off for new procedures in the current database:

sp_audit "exec_procedure", "all", "default procedure", "off"

exec_trigger

(object-specific)

all

Name of the trigger to be audited or default trigger

The database of the trigger (except tempdb)

Any command that fires the trigger

This example audits all failed executions of the trig_fix_plan trigger in the current database:

sp_audit "exec_trigger", "all", "trig_fix_plan",  "fail"

func_dbaccess

(database-specific)

all

Name of the database you are auditing

Any

Access to the database using the following functions: curunreserved_pgs, db_name, db_id, lct_admin, setdbrepstat, setrepstatus, setrepdefmode, is_repagent_enabled, rep_agent_config, rep_agent_admin

This example audits accesses to the strategy database via built-in functions:

sp_audit @option="func_dbaccess", @login_name="all", @object_name = "strategy", @setting = "on"

func_obj_access

(object-specific)

all

Name of any object that has an entry in sysobjects

Any

Access to an object using the following functions: schema_inc, col_length, col_name, data_pgs, index_col, object_id, object_name, reserved_pgs, rowcnt, used_pgs, has_subquery

This example audits accesses to the customer table via built-in functions:

sp_audit @option="func_obj_access", @login_name="all", @object_name = "customer", @setting = "on"

grant

(database-specific)

all

Name of the database to be audited

Any

grant

This example audits all grants in the planning database:

sp_audit @option="grant", @login_name="all", @object_name = "planning", @setting = "on"

insert

(object-specific)

all

Name of the view or table to which you are inserting rows, or default view or default table

The database of the object (except tempdb)

insert into a table, insert into a view

This example audits all inserts into the dpt_101_view view in the current database:

sp_audit "insert", "all", "dpt_101_view", "on"

install

(database-specific)

all

Database to be audited

Any

install java

This example audits the installation of java classes in database planning:

sp_audit "install", "all", "planning", "on"

load

(database-specific)

all

Database to be audited

Any

load database, load transaction

This example audits all failed executions of database and transaction loads in the projects_db database:

sp_audit "load", "all", "projects_db", "fail"

login

(global)

all

all

Any

Any login to Adaptive Server

This example audits all failed attempts to log in to the server:

sp_audit "login", "all", "all", "fail"

login_locked

(global)

all

all

Any

This example shows that the login is locked because of exceeding the configured number of failed login attempts:

sp_audit "login_locked", "all", "all", "on"

logout

all

all

Any

Any logout from Adaptive Server

This example turns auditing off of logouts from the server:

sp_audit "logout", "all", "all", "off"

mount

(global)

all

all

Any

mount database

This example audits all mount database commands issued:

sp_audit "mount", "all", "all", "on"

password

all

all

Any

Setting of global password and login policy options

This example turns auditing on for passwords:

sp_audit "password", "all", "all", "on"

quiesce

(global)

all

all

Any

quiesce database

This example turns auditing on for quiesce database commands:

sp_audit "quiesce", "all", "all", "on"

reference

(object-specific)

all

Name of the view or table to which you are inserting rows, or default view or default table

Any

create table, alter table

This example turns off auditing of the creation of references to the titles table:

sp_audit "reference", "all", "titles", "off"

remove

(database-specific)

all

all

Any

Audits the removal of Java classes

This example audits the removal of Java classes in the planning database:

sp_audit "remove", "all", "planning", "on"

revoke

(database-specific)

all

Database to be audited

Any

revoke

This example turns off auditing of the execution of revoke in the payments_db database:

sp_audit "revoke", "all", "payments_db", "off"

rpc

(global)

all

all

Any

Remote procedure calls (either in or out)

This example audits all remote procedure calls out of or into the server:

sp_audit "rpc", "all", "all", "on"

security

(global)

all

all

Any

Server-wide security-relevant events. See the “security” option in Table 10-5.

This example audits server-wide security-relevant events in the server:

sp_audit "security", "all", "all", "on"

select

(object-specific)

all

Name of the view or table to which you are inserting rows, or default view or default table

The database of the object (except tempdb)

select from a table, select from a view

This example audits all failed selects from the customer table in the current database:

sp_audit "select", "all", "customer", "fail"

setuser

(database-specific)

all

all

Any

setuser

This example audits all executions of setuser in the projdb database:

sp_audit "setuser", "all", "projdb", "on"

table_access

(user-specific)

Login name of the user to be audited.

all

Any

select, delete, update, or insert access in a table

This example audits all table accesses by the login named “smithson”:

sp_audit "table_access", "smithson", "all", "on"

transfer_table

(global)

all

all

Any

Server-wide option. Does not appear in sysauditoptions.

This example audits server-wide transfer-relevant events in the server:

sp_audit "transfer_table", "tdb1.table1", "all", "on"

truncate

(database-specific)

all

Database to be audited

Any

truncate table

This example audits all table truncations in the customer database:

sp_audit "truncate", "all", "customer", "on"

unbind

(database-specific)

all

Database to be audited

Any

sp_unbindefault, sp_unbindrule, sp_unbindmsg

This example audits all failed attempts of unbinding in the master database:

sp_audit "unbind", "all", "master", "fail"

unmount

(global)

all

all

Any

unmount database

This example audits all attempts to unmount or create a manifest file with any database:

sp_audit "unmount", "all", "all", "on"

update

(object-specific)

all

Name specifying the object to be audited, default table or default view

The database of the object (except tempdb)

update to a table, update to a view

This example audits all attempts by users to update the projects table in the current database:

sp_audit "update", "all", "projects", "on"

view_access

(user-specific)

Login name of the user to be audited

all

Any

select, delete, insert, or update to a view

This example turns off view auditing of user “joe”:

sp_audit "view_access", "joe", "all", "off"