Auditing options: Types and requirements

adhoc

(user-specific)

all

all

Any

Allows users to use sp_addauditrecord

Example: sp_audit "adhoc", "all", "all", "on"

(Enables ad hoc user-defined auditing records.)

all

(user-specific)

A login name or role

all

Any

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

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

(Turns auditing on for all actions in which the sa_role is active.)

alter

(database-specific)

all

Database to be audited

Any

alter database, alter table

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

(Turns auditing on for all executions of alter database and alter table in the master database.)

bcp

(database-specific)

all

Database to be audited

Any

bcp in

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

(Returns the status of bcp auditing in the pubs2 database. 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

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

(Turns bind auditing off for the planning database.)

cmdtext

(user-specific)

A login name or a role

all

Any

All actions of a particular user.(Does not reflect whether or not the text in question passed permission checks or not. eventmod always has a value of 1.)

Example sp_audit "cmdtext", "dbo", "off", all

(Turns text auditing off for Database Owners.)

create

(database-specific)

all

Database to be audited

Any

create database, create table, create procedure, create trigger, create rule, create default, sp_addmessage, create view

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

(Turns on auditing of successful object creations in the planning database. 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

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

(Audits all external accesses to the project database.)

dbcc

(global)

all

all

Any

dbcc

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

(Audits all executions of the dbcc command.)

delete

(object-specific)

all

Table or view, default table, or default view

The database of the table or view (except tempdb)

delete from a table, delete from a view

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

(Audits all delete actions for all future tables in the current database.)

disk

(global)

all

all

Any

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

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

(Audits all disk actions for the server.)

drop

(database-specific)

all

Database to be audited

Any

drop database, drop table, drop procedure, drop trigger, drop rule, drop default, sp_dropmessage, drop view

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

(Audits all drop commands in the financial database that fail permission checks.)

dump

(database-specific)

all

Database to be audited

Any

dump database, dump transaction

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

(Audits dump commands in the pubs2 database.)

errors

(global)

all

all

Any

Fatal error, non-fatal error

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

(Audits errors throughout the server.)

exec_procedure

(object-specific)

all

Procedure or default procedure

The database of the procedure (except tempdb)

execute

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

(Turns automatic auditing off of new procedures in the current database.)

exec_trigger

(object-specific)

all

Trigger or default trigger

The database of the trigger (except tempdb)

Any command that fires the trigger

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

(Audits all failed executions of the trig_fix_plan trigger in the current database.)

func_dbaccess

(database-specific)

all

Database

Any

Access to the database via Transact-SQL built-in functions

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

(Audits accesses to the strategy database via built-in functions.)

func_obj_access

(object-specific)

all

Object

Any

Access to an object via Transact-SQL built-in functions

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

(Audits accesses to the customer table via built-in functions.)

grant

(database-specific)

all

Database to be audited

Any

grant

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

(Audits all grants in the planning database.)

insert

(object-specific)

all

Table or view, default table, or default view

The database of the object (except tempdb)

insert into a table, insert into a view

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

(Audits all inserts into the dpt_101_view view in the current database.)

load

(database-specific)

all

Database to be audited

Any

load database, load transaction

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

(Audits all failed executions of database and transaction loads in the projects_db database.)

login

(global)

all

all

Any

Any login to Adaptive Server

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

(Audits all failed attempts to log in to the server.)

logout

(global)

all

all

Any

Any logout from Adaptive Server

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

(Turns auditing off of logouts from the server.)

reference

(object-specific)

all

Table to be audited

Any

Creation of a reference between tables

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

(Turns off auditing of the creation of references between the titles table and other tables.)

revoke

(database-specific)

all

Database to be audited

Any

revoke

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

(Turns off auditing of the execution of revoke in the payments_db database.)

rpc

(global)

all

all

Any

Remote procedure calls (either in or out)

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

(Audits all remote procedure calls out of or into the server.)

security

(global)

all

all

Any

Server-wide security-relevant events. See the “security” option in Table 12-3.

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

(Audits server-wide security-relevant events in the server.)

select

(object-specific)

all

Table or view, default table, or default view

The database of the object (except tempdb)

select from a table, select from a view

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

(Audits all failed selects from the customer table in the current database.)

setuser

(database-specific)

all

all

Any

setuser

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

(Audits all executions of setuser in the projdb database.)

table_access

(user-specific)

Login name

all

Any

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

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

(Audits all table accesses by the login named “smithson”.)

truncate

(database-specific)

all

Database to be audited

Any

truncate table

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

(Audits all table truncations in the customer database.)

unbind

(database-specific)

all

Database to be audited

Any

sp_unbindefault, sp_unbindrule, sp_unbindmsg

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

(Audits all failed attempts of unbinding in the master database.)

update

(object-specific)

all

View, default table, or default view

The database of the object (except tempdb)

update to a table, update to a view

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

(Audits all attempts by users to update the projects table in the current database.)

view_access

(user-specific)

Login name

all

Any

select, delete, insert, or update to a view

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

(Turns off view auditing of user “joe”.)