--Use the master database 

USE master

go 

IF OBJECT_ID('dbo.usp_Login_Audit_Trace') IS NOT NULL 

DROP PROCEDURE dbo.usp_Login_Audit_Trace 

GO 

CREATE proc usp_Login_Audit_Trace 

@path nvarchar(128), 

@duration smallint 

as 

/* 

Author: Haidong Ji 

Date: 11/08/2002 

Purpose: Login audit. Try to track which logins and which databases are accessed 

Set up trace to a file in the path specified in the @path parameter (use UNC path for network drive) for the number of days/hours/minutes specified in the @duration input parameter. 

*/ 

declare @tracestarttime datetime 

declare @traceident int 

declare @options int 

declare @filename nvarchar(245) 

declare @filesize bigint 

declare @tracestoptime datetime 

declare @createcode int 

declare @on bit 

declare @startcode int 

  

set @tracestarttime = current_timestamp 

/* Set the name of the trace file. */ 

set @filename = cast(month(current_timestamp) as varchar) + '_' + 

cast(day(current_timestamp) as varchar) + '_' + 

cast(year(current_timestamp) as varchar) + '_' + 

cast(datepart(hh, current_timestamp) as varchar) + '_' + 

cast(datepart(mi, current_timestamp) as varchar) + '_' + 

cast(datepart(ss, current_timestamp) as varchar) 

set @options = 2 

set @filename = @path + N'\' + @filename 

set @filesize = 20 

/* You can change the first parameter in the dateadd function to set how long your trace will be 

For example, if it is hh, the trace will last @duration hours */ 

set @tracestoptime = dateadd(dd, @duration, @tracestarttime) 

set @on = 1 

--set up the trace 

exec @createcode = sp_trace_create  @traceid = @traceident output,  @options = @options,  

 @tracefile = @filename,   @maxfilesize = @filesize,  @stoptime = @tracestoptime 

if @createcode = 0 

--trace created 

 begin 

 --set events and columns 

 --Trace Login event 

exec sp_trace_setevent @traceident, 14, 1, @on 

exec sp_trace_setevent @traceident, 14, 6, @on 

exec sp_trace_setevent @traceident, 14, 7, @on 

exec sp_trace_setevent @traceident, 14, 8, @on 

exec sp_trace_setevent @traceident, 14, 9, @on 

exec sp_trace_setevent @traceident, 14, 10, @on 

exec sp_trace_setevent @traceident, 14, 11, @on 

exec sp_trace_setevent @traceident, 14, 12, @on 

exec sp_trace_setevent @traceident, 14, 14, @on 

exec sp_trace_setevent @traceident, 14, 18, @on 

exec sp_trace_setevent @traceident, 14, 34, @on 

exec sp_trace_setevent @traceident, 14, 35, @on


 


 --Trace Logout event 

exec sp_trace_setevent @traceident, 15, 1, @on 

exec sp_trace_setevent @traceident, 15, 6, @on 

exec sp_trace_setevent @traceident, 15, 7, @on 

exec sp_trace_setevent @traceident, 15, 8, @on 

exec sp_trace_setevent @traceident, 15, 9, @on 

exec sp_trace_setevent @traceident, 15, 10, @on 

exec sp_trace_setevent @traceident, 15, 11, @on 

exec sp_trace_setevent @traceident, 15, 12, @on 

exec sp_trace_setevent @traceident, 15, 14, @on 

exec sp_trace_setevent @traceident, 15, 18, @on 

exec sp_trace_setevent @traceident, 15, 34, @on 

exec sp_trace_setevent @traceident, 15, 35, @on 

 --Trace Audit Object GDR event 

exec sp_trace_setevent @traceident, 103, 1, @on 

exec sp_trace_setevent @traceident, 103, 6, @on 

exec sp_trace_setevent @traceident, 103, 7, @on 

exec sp_trace_setevent @traceident, 103, 8, @on 

exec sp_trace_setevent @traceident, 103, 9, @on 

exec sp_trace_setevent @traceident, 103, 10, @on 

exec sp_trace_setevent @traceident, 103, 11, @on 

exec sp_trace_setevent @traceident, 103, 12, @on 

exec sp_trace_setevent @traceident, 103, 14, @on 

exec sp_trace_setevent @traceident, 103, 18, @on 

exec sp_trace_setevent @traceident, 103, 34, @on 

exec sp_trace_setevent @traceident, 103, 35, @on 

 --Trace Audit Add/Drop Login event 

exec sp_trace_setevent @traceident, 104, 1, @on 

exec sp_trace_setevent @traceident, 104, 6, @on 

exec sp_trace_setevent @traceident, 104, 7, @on 

exec sp_trace_setevent @traceident, 104, 8, @on 

exec sp_trace_setevent @traceident, 104, 9, @on 

exec sp_trace_setevent @traceident, 104, 10, @on 

exec sp_trace_setevent @traceident, 104, 11, @on 

exec sp_trace_setevent @traceident, 104, 12, @on 

exec sp_trace_setevent @traceident, 104, 14, @on 

exec sp_trace_setevent @traceident, 104, 18, @on 

exec sp_trace_setevent @traceident, 104, 34, @on 

exec sp_trace_setevent @traceident, 104, 35, @on 

 --Trace Audit Login GDR event 

exec sp_trace_setevent @traceident, 105, 1, @on 

exec sp_trace_setevent @traceident, 105, 6, @on 

exec sp_trace_setevent @traceident, 105, 7, @on 

exec sp_trace_setevent @traceident, 105, 8, @on 

exec sp_trace_setevent @traceident, 105, 9, @on 

exec sp_trace_setevent @traceident, 105, 10, @on 

exec sp_trace_setevent @traceident, 105, 11, @on 

exec sp_trace_setevent @traceident, 105, 12, @on 

exec sp_trace_setevent @traceident, 105, 14, @on 

exec sp_trace_setevent @traceident, 105, 18, @on 

exec sp_trace_setevent @traceident, 105, 34, @on 

exec sp_trace_setevent @traceident, 105, 35, @on 

 --Trace Audit Login Change Property event 

exec sp_trace_setevent @traceident, 106, 1, @on 

exec sp_trace_setevent @traceident, 106, 6, @on 

exec sp_trace_setevent @traceident, 106, 7, @on 

exec sp_trace_setevent @traceident, 106, 8, @on 

exec sp_trace_setevent @traceident, 106, 9, @on 

exec sp_trace_setevent @traceident, 106, 10, @on 

exec sp_trace_setevent @traceident, 106, 11, @on 

exec sp_trace_setevent @traceident, 106, 12, @on 

exec sp_trace_setevent @traceident, 106, 14, @on 

exec sp_trace_setevent @traceident, 106, 18, @on 

exec sp_trace_setevent @traceident, 106, 34, @on 

exec sp_trace_setevent @traceident, 106, 35, @on


 --Trace Audit Add Login to Server Role event 

 exec sp_trace_setevent @traceident, 108, 1, @on 

 exec sp_trace_setevent @traceident, 108, 6, @on 

 exec sp_trace_setevent @traceident, 108, 7, @on 

exec sp_trace_setevent @traceident, 108, 8, @on 

exec sp_trace_setevent @traceident, 108, 9, @on 

exec sp_trace_setevent @traceident, 108, 10, @on 

exec sp_trace_setevent @traceident, 108, 11, @on 

exec sp_trace_setevent @traceident, 108, 12, @on 

exec sp_trace_setevent @traceident, 108, 14, @on 

exec sp_trace_setevent @traceident, 108, 18, @on 

exec sp_trace_setevent @traceident, 108, 34, @on 

exec sp_trace_setevent @traceident, 108, 35, @on 

 --Trace Audit Add DB User event 

exec sp_trace_setevent @traceident, 109, 1, @on 

exec sp_trace_setevent @traceident, 109, 6, @on 

exec sp_trace_setevent @traceident, 109, 7, @on 

exec sp_trace_setevent @traceident, 109, 8, @on 

exec sp_trace_setevent @traceident, 109, 9, @on 

exec sp_trace_setevent @traceident, 109, 10, @on 

exec sp_trace_setevent @traceident, 109, 11, @on 

exec sp_trace_setevent @traceident, 109, 12, @on 

exec sp_trace_setevent @traceident, 109, 14, @on 

exec sp_trace_setevent @traceident, 109, 18, @on 

exec sp_trace_setevent @traceident, 109, 34, @on 

exec sp_trace_setevent @traceident, 109, 35, @on 

 --Trace Audit Add Member to DB event 

exec sp_trace_setevent @traceident, 110, 1, @on 

exec sp_trace_setevent @traceident, 110, 6, @on 

exec sp_trace_setevent @traceident, 110, 7, @on 

exec sp_trace_setevent @traceident, 110, 8, @on 

exec sp_trace_setevent @traceident, 110, 9, @on 

exec sp_trace_setevent @traceident, 110, 10, @on 

exec sp_trace_setevent @traceident, 110, 11, @on 

exec sp_trace_setevent @traceident, 110, 12, @on 

exec sp_trace_setevent @traceident, 110, 14, @on 

exec sp_trace_setevent @traceident, 110, 18, @on 

exec sp_trace_setevent @traceident, 110, 34, @on 

exec sp_trace_setevent @traceident, 110, 35, @on 

 --Trace Audit Add/Drop Role event 

exec sp_trace_setevent @traceident, 111, 1, @on 

exec sp_trace_setevent @traceident, 111, 6, @on 

exec sp_trace_setevent @traceident, 111, 7, @on 

exec sp_trace_setevent @traceident, 111, 8, @on 

exec sp_trace_setevent @traceident, 111, 9, @on 

exec sp_trace_setevent @traceident, 111, 10, @on 

exec sp_trace_setevent @traceident, 111, 11, @on 

exec sp_trace_setevent @traceident, 111, 12, @on 

exec sp_trace_setevent @traceident, 111, 14, @on 

exec sp_trace_setevent @traceident, 111, 18, @on 

exec sp_trace_setevent @traceident, 111, 34, @on 

exec sp_trace_setevent @traceident, 111, 35, @on 

 --filter Profiler 

 exec sp_trace_setfilter   @traceid = @traceident,   @columid = 10,    @logical_operator = 0,   @comparison_operator = 7, @value = N'SQL Profiler' 

 --start the trace 

 exec @startcode = sp_trace_setstatus  @traceid = @traceident,  @status = 1 

 if @startcode = 0 

 begin 

  select 'Trace started at ' +   cast(@tracestarttime as varchar) +    ' for ' +    cast(@duration as varchar)+ 

   ' minutes; trace id is ' +     cast(@traceident as nvarchar) +     '.'   
  end
 else
  begin
  goto Error
  end
 end
else
 begin
 goto Error
 end

return

Error:
 select 'Error starting trace.'
 return
GO