USE master
GO
IF OBJECT_ID('dbo.usp_AuditTrace_SQLStmt') IS NULL
EXEC('CREATE PROCEDURE dbo.usp_AuditTrace_SQLStmt AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.usp_AuditTrace_SQLStmt
AS /****************************************************/
/* Created by: SQL Server 2008 Profiler */
/* Date: 04/28/2010 09:58:45 AM */
/****************************************************/
-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
SET @maxfilesize = 10
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
DECLARE @ProfilePath VARCHAR(500)
SELECT @ProfilePath = ProfilePath
FROM Util.dbo.Servers
WHERE [SQLSERVERNAME] = @@SERVERNAME
DECLARE @FileName NVARCHAR(100)
SELECT @FileName = ISNULL(@ProfilePath, 'H:\Profiler\') + @@SERVICENAME + '-AuditTraceSQLStmt-'
+ REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30), GETDATE(), 120), '-', ''), ' ', '-'), ':', '')
--sp_trace_create [ @traceid = ] trace_id OUTPUT
-- , [ @options = ] option_value
-- , [ @tracefile = ] 'trace_file'
-- [ , [ @maxfilesize = ] max_file_size ]
-- [ , [ @stoptime = ] 'stop_time' ]
-- [ , [ @filecount = ] 'max_rollover_files' ]
--PRINT @FileName
EXEC @rc = sp_trace_create
@TraceID OUTPUT,
2 /* TRACE_FILE_ROLLOVER */,
@FileName,
@maxfilesize,
NULL,
10000
IF (@rc != 0)
GOTO ERROR
-- Client side File and Table cannot be scripted
-- Set the events
DECLARE @on BIT
SET @on = 1
EXEC sp_trace_setevent
@TraceID,
41,
1,
@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,
13,
@on
EXEC sp_trace_setevent
@TraceID,
41,
14,
@on
EXEC sp_trace_setevent
@TraceID,
41,
15,
@on
EXEC sp_trace_setevent
@TraceID,
41,
16,
@on
EXEC sp_trace_setevent
@TraceID,
41,
17,
@on
EXEC sp_trace_setevent
@TraceID,
41,
18,
@on
EXEC sp_trace_setevent
@TraceID,
41,
35,
@on
EXEC sp_trace_setevent
@TraceID,
41,
6,
@on
EXEC sp_trace_setevent
@TraceID,
41,
8,
@on
EXEC sp_trace_setevent
@TraceID,
41,
9,
@on
EXEC sp_trace_setevent
@TraceID,
45,
1,
@on
EXEC sp_trace_setevent
@TraceID,
45,
10,
@on
EXEC sp_trace_setevent
@TraceID,
45,
11,
@on
EXEC sp_trace_setevent
@TraceID,
45,
12,
@on
EXEC sp_trace_setevent
@TraceID,
45,
13,
@on
EXEC sp_trace_setevent
@TraceID,
45,
14,
@on
EXEC sp_trace_setevent
@TraceID,
45,
15,
@on
EXEC sp_trace_setevent
@TraceID,
45,
16,
@on
EXEC sp_trace_setevent
@TraceID,
45,
17,
@on
EXEC sp_trace_setevent
@TraceID,
45,
18,
@on
EXEC sp_trace_setevent
@TraceID,
45,
35,
@on
EXEC sp_trace_setevent
@TraceID,
45,
6,
@on
EXEC sp_trace_setevent
@TraceID,
45,
8,
@on
EXEC sp_trace_setevent
@TraceID,
45,
9,
@on
-- Set the Filters
EXEC sp_trace_setfilter
@TraceID,
1,
0,
7,
N'insert bulk%'
EXEC sp_trace_setfilter
@TraceID,
1,
0,
7,
N'%exec %'
EXEC sp_trace_setfilter
@TraceID,
1,
0,
7,
N'Changed database context to ''%''.'
EXEC sp_trace_setfilter
@TraceID,
1,
0,
7,
N'Changed language setting to us_english.'
EXEC sp_trace_setfilter
@TraceID,
1,
0,
7,
N'exec sp_reset_connection'
EXEC sp_trace_setfilter
@TraceID,
1,
0,
7,
N'select collationname(%)'
EXEC sp_trace_setfilter
@TraceID,
1,
0,
7,
N'exec [sys].sp_oledb_ro_usrname'
EXEC sp_trace_setfilter
@TraceID,
1,
0,
7,
N'%exec GetDBVersion @DBVersion=@p1 output%'
EXEC sp_trace_setfilter
@TraceID,
1,
0,
7,
N'Warning: Null value is eliminated by an aggregate or other SET operation.'
EXEC sp_trace_setfilter
@TraceID,
1,
0,
7,
N'SELECT N''Testing Connection...'''
EXEC sp_trace_setfilter
@TraceID,
1,
0,
7,
N' set quoted_identifier off '
EXEC sp_trace_setfilter
@TraceID,
1,
0,
7,
N'select @@microsoftversion'
EXEC sp_trace_setfilter
@TraceID,
1,
0,
7,
N'SELECT ISNULL(SUSER_SNAME(), SUSER_NAME())'
EXEC sp_trace_setfilter
@TraceID,
1,
0,
7,
N'select convert(sysname, serverproperty(N''servername''))'
EXEC sp_trace_setfilter
@TraceID,
1,
0,
7,
N'SET TEXTSIZE 1024'
EXEC sp_trace_setfilter
@TraceID,
1,
0,
7,
N'exec GetMyRunningJobs @ComputerName=N''%'',@JobType=[0-9]'
EXEC sp_trace_setfilter
@TraceID,
1,
0,
7,
N'auto statistics internal'
EXEC sp_trace_setfilter
@TraceID,
1,
0,
7,
N'exec sp_executesql N''Select ConfiguredValue, PackagePath, ConfiguredValueType from%'
EXEC sp_trace_setfilter
@TraceID,
1,
0,
7,
N'exec [sys].sp_oledb_ro_usrname'
EXEC sp_trace_setfilter
@TraceID,
1,
0,
7,
N'There are already statistics on table ''%'' named ''%''.'
EXEC sp_trace_setfilter
@TraceID,
10,
0,
7,
N'SQL Server Profiler%'
EXEC sp_trace_setfilter
@TraceID,
10,
0,
7,
N'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'
EXEC sp_trace_setfilter
@TraceID,
10,
0,
7,
N'SQLAgent - Update job activity'
EXEC sp_trace_setfilter
@TraceID,
10,
0,
7,
N'SQLAgent - Job invocation engine'
EXEC sp_trace_setfilter
@TraceID,
10,
0,
7,
N'SQLAgent - Alert Engine'
EXEC sp_trace_setfilter
@TraceID,
10,
0,
7,
N'SQLAgent - Schedule Saver'
EXEC sp_trace_setfilter
@TraceID,
10,
0,
7,
N'SQLAgent - Step History Logger'
EXEC sp_trace_setfilter
@TraceID,
10,
0,
7,
N'SQLAgent - Job Manager'
EXEC sp_trace_setfilter
@TraceID,
10,
0,
7,
N'Microsoft SQL Server Management Studio'
EXEC sp_trace_setfilter
@TraceID,
10,
0,
7,
N'Microsoft SQL Server'
EXEC sp_trace_setfilter
@TraceID,
10,
0,
7,
N'DatabaseMail - SQLAGENT%'
EXEC sp_trace_setfilter
@TraceID,
10,
0,
7,
N'Red Gate Software%'
EXEC sp_trace_setfilter
@TraceID,
35,
0,
7,
N'ReportServer%'
EXEC sp_trace_setfilter
@TraceID,
35,
0,
7,
N'msdb%'
DECLARE @bigintfilter BIGINT = 10000000
EXEC sp_trace_setfilter
@TraceID,
13,
0,
4,
@bigintfilter
-- 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'usp_AuditTrace_SQLStmt', 'startup', 'on'
GO