1, Windows event log

2, SQL Server Error log

3, Use c2 audit option

EXEC sp_configure 'show advanced option', '1'

RECONFIGURE

EXEC sp_configure 'c2 audit mode',1

RECONFIGURE

4, using the customized script

As I mentioned earlier, whenever SQL Server restarts, all trace configuration information is lost and requires reentry—a time-consuming and problematic proposition. You need to reenter the commands necessary to enable tracing as soon as the server starts but before a user logs on. Listing 3 is a stored procedure that combines Web Listing 1 and Web Listing 2 (replacing traceid with the value that TraceIdOut returns) with the necessary invocation of sp_trace_setstatus.

Web Listing 3: Script to Create and Configure a Trace

DEFINE @TraceIdOut int
DEFINE @On bit
SET @On = 1
exec sp_trace_create @TraceIdOut OUTPUT, 6, N'c:sqlauditfile'
exec sp_trace_setevent @TraceIdOut, 14, 6, @On
exec sp_trace_setevent @TraceIdOut, 14, 7, @On
exec sp_trace_setevent @TraceIdOut, 14, 8, @On
exec sp_trace_setevent @TraceIdOut, 14, 9, @On
exec sp_trace_setevent @TraceIdOut, 14, 10, @On
exec sp_trace_setevent @TraceIdOut, 15, 6, @On
exec sp_trace_setevent @TraceIdOut, 15, 7, @On
exec sp_trace_setevent @TraceIdOut, 15, 8, @On
exec sp_trace_setevent @TraceIdOut, 15, 9, @On
exec sp_trace_setevent @TraceIdOut, 15, 10, @On
exec sp_trace_setevent @TraceIdOut, 20, 6, @On
exec sp_trace_setevent @TraceIdOut, 20, 7, @On
exec sp_trace_setevent @TraceIdOut, 20, 8, @On
exec sp_trace_setevent @TraceIdOut, 20, 9, @On
exec sp_trace_setevent @TraceIdOut, 20, 10, @On

To configure this stored procedure to run at SQL Server start-up, use the stored procedure sp_procoption as follows:

exec sp_procoption
N'<procname>', 'startup', 'on'

After you're comfortable using server-side trace files to record security-related activity on your database server, you can experiment with filters. You use the sp_trace_setfilter stored procedure to define filters, which let you—among other things—record only activity that matches a certain pattern, such as a SQL verb (e.g., SELECT, INSERT, UPDATE, DELETE), or that involve a particular object (e.g., a specific table).

Monitor the contents of the logs that SQL Server traces and filters generate. Over time, you'll be able to craft an auditing policy that meets the needs of your organization and minimizes the performance impact that's typically associated with auditing. If, however, your database systems are used infrequently and have sufficient capacity, you might be able to do without traces and filters and stick with the C2 auditing mode.

More details? See http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx