Server Side Trace with Security Audit Event Class Events

DECLARE @return_code INT;

DECLARE @TraceID INT;

DECLARE @maxfilesize BIGINT;

SET @maxfilesize = 5;

--step 1: create a new empty trace definition

EXEC sp_trace_create

                @traceid OUTPUT

               , @options = 2

               , @tracefile = N'C:\TraceFiles\LoginAudit21'

               , @maxfilesize = @maxfilesize

    , @stoptime =NULL

    , @filecount = 2;

-- step 2: add the events and columns

EXEC sp_trace_setevent

                @traceid = @TraceID

               , @eventid = 14 -- Security Audit

               , @columnid = 10 -- Application Name

               , @on = 1;--include this column in trace

EXEC sp_trace_setevent

                @traceid = @TraceID

               , @eventid = 14 -- Security Audit

               , @columnid = 35 --Database Name

               , @on = 1;--include this column in trace

EXEC sp_trace_setevent

                @traceid = @TraceID

               , @eventid = 14 -- Security Audit

               , @columnid = 11 --Login Name

               , @on = 1;--include this column in trace  

EXEC sp_trace_setevent

                @traceid = @TraceID

               , @eventid = 14 -- Security Audit

               , @columnid = 6 -- NTUserName

               , @on = 1;--include this column in trace

EXEC sp_trace_setevent

                @traceid = @TraceID

               , @eventid = 14 -- Security Audit

               , @columnid = 12 --SPID

               , @on = 1;--include this column in trace

EXEC sp_trace_setevent

                @traceid = @TraceID

               , @eventid = 14 -- Security Audit

               , @columnid = 14 --EndTime

               , @on = 1;--include this column in trace        

-- step 3: add duration filter

--DECLARE @DurationFilter BIGINT;

--SET @DurationFilter = 10000000; --duration in microseconds

--EXEC sp_trace_setfilter

--                @traceid = @TraceID

--               , @columnid = 13

--               , @logical_operator = 0 --AND

--               , @comparison_operator = 4 -- greater than or equal to

--               , @value = @DurationFilter; --filter value

--SELECT @TraceID AS TraceID;