Archive

Posts Tagged ‘sql server database audit’

SQL server database audit

September 14, 2008 Leave a comment

We had a requirement to audit any SQL actions a user executes on any table, by loggin into SQL Query Analyser. We used SQL profiler to create a script as given below. Using the script, we created a new procedure in master database with an automatic start-on-restart option turned on. Once we restart sql server, the audit process start writing to a file which can be opened from SQL profiler. This file starts a new file once it reaches the file size maximum limit defined in the script or whenever SQL Server is rebooted. You may modify this script based on your requirement.

CREATE PROCEDURE dbo.dba_startup_audit  AS
— Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
DECLARE @File NVARCHAR(100)
Declare @Year nvarchar(10)
Declare @Month nvarchar(10)
Declare @Day nvarchar(10)
Declare @Hour nvarchar(10)
Declare @Minute nvarchar(10)
set @Year = DATEPART(year,GETDATE())
set @Month = DATEPART(month,GETDATE())
set @Day = DATEPART(day, GETDATE())
set @Hour = DATEPART(hour, GETDATE())
set @Minute = DATEPART(minute, GETDATE())
SET @File = N’C:\temp\db_audit_trace’+@Year+@Month+@Day+@Hour+@Minute
set @maxfilesize = 10
exec @rc = sp_trace_create @TraceID output, 2, @File, @maxfilesize, NULL
if (@rc != 0) goto error

— Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 22, @on
exec sp_trace_setevent @TraceID, 10, 35, @on

exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 2, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 22, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 41, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 2, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 8, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 22, @on
exec sp_trace_setevent @TraceID, 13, 35, @on
exec sp_trace_setevent @TraceID, 13, 41, @on

exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 2, @on
exec sp_trace_setevent @TraceID, 14, 6, @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, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 22, @on
exec sp_trace_setevent @TraceID, 14, 35, @on
exec sp_trace_setevent @TraceID, 14, 41, @on

exec sp_trace_setevent @TraceID, 15, 1, @on
exec sp_trace_setevent @TraceID, 15, 2, @on
exec sp_trace_setevent @TraceID, 15, 6, @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, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 22, @on
exec sp_trace_setevent @TraceID, 15, 35, @on
exec sp_trace_setevent @TraceID, 15, 41, @on

exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 8, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 22, @on
exec sp_trace_setevent @TraceID, 17, 35, @on
exec sp_trace_setevent @TraceID, 17, 41, @on

exec sp_trace_setevent @TraceID, 40, 1, @on
exec sp_trace_setevent @TraceID, 40, 2, @on
exec sp_trace_setevent @TraceID, 40, 6, @on
exec sp_trace_setevent @TraceID, 40, 8, @on
exec sp_trace_setevent @TraceID, 40, 10, @on
exec sp_trace_setevent @TraceID, 40, 11, @on
exec sp_trace_setevent @TraceID, 40, 12, @on
exec sp_trace_setevent @TraceID, 40, 14, @on
exec sp_trace_setevent @TraceID, 40, 22, @on
exec sp_trace_setevent @TraceID, 40, 35, @on

exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 2, @on
exec sp_trace_setevent @TraceID, 41, 6, @on
exec sp_trace_setevent @TraceID, 41, 8, @on
exec sp_trace_setevent @TraceID, 41, 10, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 41, 22, @on
exec sp_trace_setevent @TraceID, 41, 35, @on

exec sp_trace_setevent @TraceID, 46, 1, @on
exec sp_trace_setevent @TraceID, 46, 2, @on
exec sp_trace_setevent @TraceID, 46, 6, @on
exec sp_trace_setevent @TraceID, 46, 8, @on
exec sp_trace_setevent @TraceID, 46, 10, @on
exec sp_trace_setevent @TraceID, 46, 11, @on
exec sp_trace_setevent @TraceID, 46, 12, @on
exec sp_trace_setevent @TraceID, 46, 14, @on
exec sp_trace_setevent @TraceID, 46, 22, @on
exec sp_trace_setevent @TraceID, 46, 34, @on

exec sp_trace_setevent @TraceID, 47, 1, @on
exec sp_trace_setevent @TraceID, 47, 2, @on
exec sp_trace_setevent @TraceID, 47, 6, @on
exec sp_trace_setevent @TraceID, 47, 8, @on
exec sp_trace_setevent @TraceID, 47, 10, @on
exec sp_trace_setevent @TraceID, 47, 11, @on
exec sp_trace_setevent @TraceID, 47, 12, @on
exec sp_trace_setevent @TraceID, 47, 14, @on
exec sp_trace_setevent @TraceID, 47, 22, @on
exec sp_trace_setevent @TraceID, 47, 35, @on

exec sp_trace_setevent @TraceID, 82, 1, @on
exec sp_trace_setevent @TraceID, 82, 6, @on
exec sp_trace_setevent @TraceID, 82, 8, @on
exec sp_trace_setevent @TraceID, 82, 9, @on
exec sp_trace_setevent @TraceID, 82, 10, @on
exec sp_trace_setevent @TraceID, 82, 11, @on
exec sp_trace_setevent @TraceID, 82, 12, @on
exec sp_trace_setevent @TraceID, 82, 14, @on
exec sp_trace_setevent @TraceID, 82, 22, @on
exec sp_trace_setevent @TraceID, 82, 35, @on
exec sp_trace_setevent @TraceID, 82, 41, @on

exec sp_trace_setevent @TraceID, 104, 1, @on
exec sp_trace_setevent @TraceID, 104, 2, @on
exec sp_trace_setevent @TraceID, 104, 6, @on
exec sp_trace_setevent @TraceID, 104, 8, @on
exec sp_trace_setevent @TraceID, 104, 10, @on
exec sp_trace_setevent @TraceID, 104, 11, @on
exec sp_trace_setevent @TraceID, 104, 12, @on
exec sp_trace_setevent @TraceID, 104, 14, @on
exec sp_trace_setevent @TraceID, 104, 22, @on
exec sp_trace_setevent @TraceID, 104, 35, @on

— Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N’People%’
exec sp_trace_setfilter @TraceID, 10, 0, 7, N’sqlmon.pl’
exec sp_trace_setfilter @TraceID, 10, 0, 7, N’SQL Profiler’
exec sp_trace_setfilter @TraceID, 10, 0, 7, N’SQLAgent%’
exec sp_trace_setfilter @TraceID, 10, 0, 7, N’MS SQLEM%’
exec sp_trace_setfilter @TraceID, 10, 0, 7, N’sqlsvr_collector.pl%’
exec sp_trace_setfilter @TraceID, 10, 0, 7, N’sqlsvr_backup_collector.pl%’
exec sp_trace_setfilter @TraceID, 10, 0, 7, N’ps_app_version_pop.pl%’
exec sp_trace_setfilter @TraceID, 10, 0, 7, N’SQLDMO%’
exec sp_trace_setfilter @TraceID, 10, 1, 6, N’SQL Query Analyzer%’
exec sp_trace_setfilter @TraceID, 1, 1, 6, N’Select%’
exec sp_trace_setfilter @TraceID, 1, 1, 6, N’Insert%’
exec sp_trace_setfilter @TraceID, 1, 1, 6, N’Update%’
exec sp_trace_setfilter @TraceID, 1, 1, 6, N’Create%’
exec sp_trace_setfilter @TraceID, 1, 1, 6, N’Drop%’
exec sp_trace_setfilter @TraceID, 1, 1, 6, N’Delete%’
–set @intfilter = 100
–exec sp_trace_setfilter @TraceID, 22, 0, 4, @intfilter
–exec sp_trace_setfilter @TraceID, 35, 1, 6, N’HR83FS’

— Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
— display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
GO
exec sp_procoption N’dba_startup_audit’, N’startup’, N’true’
GO