1> sp_auditoption "enable auditing", "on"
2> go

1. databases: sp_auditdatabase: only logs big database events (drop database, truncate table, drop table, etc)
2. objects: sp_auditobject: on a table/view gives select/insert/update/delete request auditing. Not so good b/c it only audits that there was a select REQUEST, not the actual code. Example:

   1> sp_auditobject tablename, db_name, "both", "s"
   2> go
   

   this audits failed and successful attempts to do selects on the table tablename in database db_name.
   
   

   this ended up being bad having the failed select attempts b/c we were logging some ct_results() pending errors that were huge; so i turned off failures. The only way to turn off individual ok/fail/both is to clear them by turning everything off then reset.

   sp_auditobject [table] will show status of table-level commands
   
   
3. stored procedures: sp_auditsproc; Only an option for database that use stored procedures. In the times I've installed auditing, the applications did not use stored procs to any great extent.
   
   
4. logins: sp_auditlogin; w/ cmdtext option gives exact query syntax requested. Gives the level of what we want.

   1> sp_auditlogin sa, "cmdtext", "on"
   2> go
   1> sp_auditlogin user, "cmdtext", "on"
   2> go
   

   sp_auditlogin by itself shows status of all logins; table/view/cmdtext are options

How do i see the audit trail?

Only users w/ sso_role can view the sybsecurity..sysaudits table where the audit trail resides. You can perform a select from this table and delimit your query by any of several useful fields (loginname, database name, object or table name, eventtime, etc). Example sysaudits queries are:

1> select loginname,extrainfo from sysaudits where dbname="db_name"
2> go
1> select dbname,objname,extrainfo from sysaudits where loginname="sa"
2> go
1> select loginname,dbname,objname from sysaudits where eventtime>"9/4/97 12:00"
2> go  

Archiving/Cleaning up the audit trail: very important: if you don't have a process to truncate the sysaudits table, you WILL crash your application users. Guaranteed. Even worse, often the whole server crashes...so archiving is important

Here's our plan: we have a special sp_thresholdaction process in place that looks like the following:

use sybsecurity
go

if exists (select * from sysobjects where name = "sp_thresholdaction"
           and type = 'P')
   drop proc sp_thresholdaction
go

create procedure sp_thresholdaction
@dbname varchar(30),
@segmentname varchar(30),
@space_left int,
@status int
as                                            

insert db_name..sysaudits_db_name
select loginname,dbname,objname,extrainfo
from sysaudits where dbname="db_name"

dump transaction @dbname with truncate_only
truncate table sysaudits

print "LCT fired on '%1!' with truncate_only and sysaudit table truncate",
 @dbname

go

Notice this will insert all audit records we desire to keep (i.e., all for the database db_name) into a holding table in db_name, then truncates sysaudits before truncating the log. the db_name..sysaudits_db_name table MUST be existant for this process to work, thus if you are cleaning up db_name..sysaudits_db_name just truncate it (don't drop it). The text of the queries being run is in the "extrainfo" field; the other three fields should be self-explaitory

Performance/Tuning Aspects: tune the audit queue size through sp_configure: if its too small the system will slow down actual transactions...caveat: when large, audit records are buffered which can mean data loss during server crash.

We're getting thousands of these messages in sysaudits...why?

Open Client Message
Layer 1, Origin 1, Severity 1, Number 163
ct_results(): user api layer: external error: This routine cannot be called
until all fetchable results have been completely processed.