How to enable SQL 2000 Database Auditing

This article provides you with the steps to enable database auditing on Microsoft SQL 2000.

Configuration process for database auditing on the database-side (MS SQL) consists of the following steps:

1. Create directory for the trace files (ex: C:\DbTrace). Trace files contain following audit data:
   
   • The data that was modified
   • Start and End times of the transaction
   • Name of Application used
   • Name of Database affected
   • Host where transaction originated
   • Login name of the user
2. Create stored procedure to start trace that will capture auditing data and copy it to the trace file.
3. Configure SQL server to execute previously created stored procedure on startup.
4. Start database auditing by running previously created stored procedure.

   EXEC master.dbo.DbTraceUserActivity;   

5. Verify trace status output by running:

   select * from ::fn_trace_getinfo (default)

   Please remember {traceid} value from the above command. It will be needed to stop the trace.
6. In case you need to stop the trace for any reason use the below code. Remember that {traceid} is set to 2.
   

   declare @TraceID int set @TraceID = 2 exec sp_trace_setstatus @TraceID, 0 exec sp_trace_setstatus @TraceID, 2   

7. In case you need to remove/re-create the above stored procedure:
   

   USE master GO DROP PROCEDURE dbo.DbTraceUserActivity; GO   

8. To view content of the trace, issue the following command:
   

   USE master GO SELECT * FROM ::fn_trace_gettable('C:\DbTrace\DbTraceUserActivity',default) GO   

Create stored procedure to capture auditing data

/****************************************************/
use master
go

drop procedure dbo.DbTraceUserActivity;
go

CREATE procedure DbTraceUserActivity 
as

set nocount on

-- Create a Queue
declare @rc int
declare @maxfilesize bigint
declare @j int
declare @i int
declare @Events varchar(400)
declare @TmpCols varchar(300)
declare @TraceFileName nvarchar(245)
set @maxfilesize = 250

DECLARE @TraceID int
DECLARE @On bit
DECLARE @FileDate varchar(32)
DECLARE @Event int, @Col int, @OnOff bit
DECLARE @ColStr varchar(300)

SET @FileDate = CONVERT(CHAR(8),GETDATE(),112) + REPLACE(CONVERT(varchar(15),GETDATE(),114),':','')
SET @Events='117,14,105,104,108,107,106,113,46,118,47,18,109,110,111,112,'
SET @TmpCols='1,3,8,10,11,12,14,15,21,22,23,28,29,34,35,38,39,40,42,'
SET @TraceFileName = 'C:\DbTrace\DbTraceUserActivity' + @FileDate

exec @rc = sp_trace_create @TraceID OUTPUT, 0, @TraceFileName, @maxfilesize, NULL
if (@rc != 0) goto error

IF @Events IS NOT NULL BEGIN
SET @OnOff=1
SET @i=CHARINDEX(',',@Events)
WHILE @i<>0 BEGIN
SET @Event=CAST(LEFT(@Events,@i-1) AS int)
SET @ColStr=@TmpCols
SET @j=CHARINDEX(',',@ColStr)
WHILE @j<>0 BEGIN
SET @Col=CAST(LEFT(@ColStr,@j-1) AS int)
EXEC @rc=sp_trace_setevent @TraceID=@TraceID,@EventId=@Event,@ColumnId=@Col,@on=@OnOff
SET @ColStr=SUBSTRING(@ColStr,@j+1,300)
SET @j=CHARINDEX(',',@ColStr)
END
SET @Events=SUBSTRING(@Events,@i+1,300)
SET @i=CHARINDEX(',',@Events)
END
END

exec sp_trace_setstatus @TraceID, 0

EXEC @rc=sp_trace_setfilter @TraceId=@TraceID, @columnid=10, @logical_operator=0, @comparison_operator=7, @value=N'Monitoring Software'
if (@rc != 0) goto error
EXEC @rc=sp_trace_setfilter @TraceId=@TraceID, @columnid=36, @logical_operator=1, @comparison_operator=6, @value=N'%SOMEOTHERDB%'
if (@rc != 0) goto error
EXEC @rc=sp_trace_setfilter @TraceId=@TraceID, @columnid=36, @logical_operator=1, @comparison_operator=6, @value=N'%master%'
if (@rc != 0) goto error

exec sp_trace_setstatus @TraceID, 1

goto finish

error: 
select ErrorCode=@rc

finish:
GO

/****************************************************/

Configure SQL server to execute previously created stored procedure on startup

USE MASTER GO EXEC SP_PROCOPTION DbTraceUserActivity, 'STARTUP', 'ON' GO