Created a new procedure in master database with an automatic
start-on-restart option turned on.
exec sp_procoption N’dba_startup_audit’, N’startup’, N’true’
exec sp_procoption N’dba_startup_audit’, N’startup’, N’true’
GO
Execute above command once you create below procedure
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
– 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
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, 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, 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, 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, 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, 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, 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, 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, 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, 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
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
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’
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_trace_setstatus @TraceID, 1
– display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
GO
