|
|
How to Record SQL Server Security Audit Trace from background
(Please note: the double quotes are supposed to be single quotes in the example scripts/SQL statements below.)
First of all, the script should create a trace:
DEFINE @TraceIdOut int
exec sp_trace_create @TraceIdOut OUTPUT,6, N"c:sqlauditfile"
PRINT @TraceIdOut
sp_trace_create starts a trace with trace number eturned from @TraceIdOut and the TraceOutput at N"c:sqlauditfile"
Then we can run this script by doing:
osql —E —S —i createtrace.sql
Where createtrace.sql contains the sp_trace_create and other statements.
To trace the events we want, we will need to enhance createtrace.sql by adding sp_trace_setevent:
DEFINE @On bit
SET @On = 1
exec sp_trace_setevent traceid, 14, 6, @On
exec sp_trace_setevent traceid, 14, 7, @On
exec sp_trace_setevent traceid, 14, 8, @On
exec sp_trace_setevent traceid, 14, 9, @On
exec sp_trace_setevent traceid, 14, 10, @On
exec sp_trace_setevent traceid, 15, 6, @On
exec sp_trace_setevent traceid, 15, 7, @On
exec sp_trace_setevent traceid, 15, 8, @On
exec sp_trace_setevent traceid, 15, 9, @On
exec sp_trace_setevent traceid, 15, 10, @On
exec sp_trace_setevent traceid, 20, 6, @On
exec sp_trace_setevent traceid, 20, 7, @On
exec sp_trace_setevent traceid, 20, 8, @On
exec sp_trace_setevent traceid, 20, 9, @On
exec sp_trace_setevent traceid, 20, 10, @On
Run this created script by doing:
osql —E —S —i createtrace.sql
Or, you can let SQL Server run it whenever Server starts up: exec sp_procoption N"", "startup", "on" Where is the name of the stored procedure saved from createtrace.sql
The content of the final script createtrace.sql is like:
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
PRINT @TraceIdOut
sp_trace_create starts a trace with trace number eturned from @TraceIdOut and the TraceOutput at N"c:sqlauditfile"
Then we can run this scrept by doing:
osql —E —S —i createtrace.sql
Where createtrace.sql contains the sp_trace_create and other statements.
To trace the events we want, we will need to enhance createtrace.sql by adding sp_trace_setevent:
DEFINE @On bit
SET @On = 1
exec sp_trace_setevent traceid, 14, 6, @On
exec sp_trace_setevent traceid, 14, 7, @On
exec sp_trace_setevent traceid, 14, 8, @On
exec sp_trace_setevent traceid, 14, 9, @On
exec sp_trace_setevent traceid, 14, 10, @On
exec sp_trace_setevent traceid, 15, 6, @On
exec sp_trace_setevent traceid, 15, 7, @On
exec sp_trace_setevent traceid, 15, 8, @On
exec sp_trace_setevent traceid, 15, 9, @On
exec sp_trace_setevent traceid, 15, 10, @On
exec sp_trace_setevent traceid, 20, 6, @On
exec sp_trace_setevent traceid, 20, 7, @On
exec sp_trace_setevent traceid, 20, 8, @On
exec sp_trace_setevent traceid, 20, 9, @On
exec sp_trace_setevent traceid, 20, 10, @On
Run this created script by doing:
osql —E —S —i createtrace.sql
Or, you can let SQL Server run it whenever Server starts up: exec sp_procoption N"", "startup", "on" Where is the name of the stored procedure saved from createtrace.sql
The content of the final script createtrace.sql is like:
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
Once Trace starts, you will need to have a way to stop/start/close it. The stored procedure sp_trace_setstatus has two parameters. The first one is the obtained trace id, and the 2nd one has the following meanings:
If it is 0, stop trace.
If it is 1, (re)start trace.
It it is 2, close the trace and delete the definition. You have to stop (do 0) before you close (do 2).
|
