Hi,
There is one mistake in the beginning of the scripts. Instead of DROP PROCEDURE usp_Login_Audit_Trace, the article says DROP PROCEDURE dbo.spRoleMembers. My apologies and much thanks to reader William Nabers. Got to be careful with "cut and paste"....
The correct script is at the end of this post.
In addition, there might be some compilation errors when you copy and paste the script to Query Analyzer. I think that's due to changes to formatting during the upload of the article to the web server. You might need to make some minor adjustment in order for it to work.
Thanks.
Haidong
--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