April 4, 2011 at 10:18 am
Evening all,
I'm trying to set up and SQL server trace using the create trace stored procedure. I generated the script in SQL Profiler and exported it.
In a nutshell what I want is to
a) filter for one specific database
b) filter for a couple of individuals
c) filter only security audit events
I have the problem, that the filters do not seem to be getting applied.
a) I can see more than 1 db in the log and
b) it's logging all user activity.
I have seen a few post here and there that Profiler produced incorrect trace files, but this was fixed in SP2. I'm on SP4 and it seems to be a similiar issue.
can those more exeperienced with tracing, tell me if anything looks incorrect in my script:
/****************************************************/
/* Created by: SQL Server Profiler 2005 */
/* Date: 17/03/2011 16:07:45 */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @tracefile_name nvarchar(256)
declare @maxfilesize bigint
set @maxfilesize = 500
-- 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
set @tracefile_name = CAST(N'\\fileserver\db_audit_trace_logs\commtrac_grain_pwc_audit_log_wk' + RIGHT('00' + cast(DATEPART(wk,GETDATE()) as varchar(2)),2) + '_' + REPLACE(REPLACE(REPLACE(CONVERT(nvarchar(16), GETDATE(), 120),' ','_'),':',''),'-','') AS VARCHAR(256))
exec @rc = sp_trace_create @TraceID output, 0,@tracefile_name , @maxfilesize, NULL
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, 172, 7, @on
exec sp_trace_setevent @TraceID, 172, 23, @on
exec sp_trace_setevent @TraceID, 172, 39, @on
exec sp_trace_setevent @TraceID, 172, 8, @on
exec sp_trace_setevent @TraceID, 172, 40, @on
exec sp_trace_setevent @TraceID, 172, 64, @on
exec sp_trace_setevent @TraceID, 172, 1, @on
exec sp_trace_setevent @TraceID, 172, 9, @on
exec sp_trace_setevent @TraceID, 172, 41, @on
exec sp_trace_setevent @TraceID, 172, 49, @on
exec sp_trace_setevent @TraceID, 172, 10, @on
exec sp_trace_setevent @TraceID, 172, 26, @on
exec sp_trace_setevent @TraceID, 172, 34, @on
exec sp_trace_setevent @TraceID, 172, 42, @on
exec sp_trace_setevent @TraceID, 172, 50, @on
exec sp_trace_setevent @TraceID, 172, 3, @on
exec sp_trace_setevent @TraceID, 172, 11, @on
exec sp_trace_setevent @TraceID, 172, 19, @on
exec sp_trace_setevent @TraceID, 172, 35, @on
exec sp_trace_setevent @TraceID, 172, 43, @on
exec sp_trace_setevent @TraceID, 172, 51, @on
exec sp_trace_setevent @TraceID, 172, 4, @on
exec sp_trace_setevent @TraceID, 172, 12, @on
exec sp_trace_setevent @TraceID, 172, 28, @on
exec sp_trace_setevent @TraceID, 172, 60, @on
exec sp_trace_setevent @TraceID, 172, 21, @on
exec sp_trace_setevent @TraceID, 172, 37, @on
exec sp_trace_setevent @TraceID, 172, 6, @on
exec sp_trace_setevent @TraceID, 172, 14, @on
exec sp_trace_setevent @TraceID, 129, 7, @on
exec sp_trace_setevent @TraceID, 129, 23, @on
exec sp_trace_setevent @TraceID, 129, 8, @on
exec sp_trace_setevent @TraceID, 129, 40, @on
exec sp_trace_setevent @TraceID, 129, 64, @on
exec sp_trace_setevent @TraceID, 129, 1, @on
exec sp_trace_setevent @TraceID, 129, 41, @on
exec sp_trace_setevent @TraceID, 129, 49, @on
exec sp_trace_setevent @TraceID, 129, 6, @on
exec sp_trace_setevent @TraceID, 129, 10, @on
exec sp_trace_setevent @TraceID, 129, 14, @on
exec sp_trace_setevent @TraceID, 129, 26, @on
exec sp_trace_setevent @TraceID, 129, 34, @on
exec sp_trace_setevent @TraceID, 129, 50, @on
exec sp_trace_setevent @TraceID, 129, 3, @on
exec sp_trace_setevent @TraceID, 129, 11, @on
exec sp_trace_setevent @TraceID, 129, 35, @on
exec sp_trace_setevent @TraceID, 129, 51, @on
exec sp_trace_setevent @TraceID, 129, 4, @on
exec sp_trace_setevent @TraceID, 129, 12, @on
exec sp_trace_setevent @TraceID, 129, 28, @on
exec sp_trace_setevent @TraceID, 129, 60, @on
exec sp_trace_setevent @TraceID, 129, 21, @on
exec sp_trace_setevent @TraceID, 129, 37, @on
exec sp_trace_setevent @TraceID, 133, 7, @on
exec sp_trace_setevent @TraceID, 133, 23, @on
exec sp_trace_setevent @TraceID, 133, 8, @on
exec sp_trace_setevent @TraceID, 133, 40, @on
exec sp_trace_setevent @TraceID, 133, 64, @on
exec sp_trace_setevent @TraceID, 133, 1, @on
exec sp_trace_setevent @TraceID, 133, 41, @on
exec sp_trace_setevent @TraceID, 133, 49, @on
exec sp_trace_setevent @TraceID, 133, 6, @on
exec sp_trace_setevent @TraceID, 133, 10, @on
exec sp_trace_setevent @TraceID, 133, 14, @on
exec sp_trace_setevent @TraceID, 133, 26, @on
exec sp_trace_setevent @TraceID, 133, 34, @on
exec sp_trace_setevent @TraceID, 133, 38, @on
exec sp_trace_setevent @TraceID, 133, 50, @on
exec sp_trace_setevent @TraceID, 133, 3, @on
exec sp_trace_setevent @TraceID, 133, 11, @on
exec sp_trace_setevent @TraceID, 133, 19, @on
exec sp_trace_setevent @TraceID, 133, 35, @on
exec sp_trace_setevent @TraceID, 133, 51, @on
exec sp_trace_setevent @TraceID, 133, 4, @on
exec sp_trace_setevent @TraceID, 133, 12, @on
exec sp_trace_setevent @TraceID, 133, 28, @on
exec sp_trace_setevent @TraceID, 133, 60, @on
exec sp_trace_setevent @TraceID, 133, 21, @on
exec sp_trace_setevent @TraceID, 133, 37, @on
exec sp_trace_setevent @TraceID, 130, 7, @on
exec sp_trace_setevent @TraceID, 130, 23, @on
exec sp_trace_setevent @TraceID, 130, 39, @on
exec sp_trace_setevent @TraceID, 130, 8, @on
exec sp_trace_setevent @TraceID, 130, 40, @on
exec sp_trace_setevent @TraceID, 130, 64, @on
exec sp_trace_setevent @TraceID, 130, 1, @on
exec sp_trace_setevent @TraceID, 130, 41, @on
exec sp_trace_setevent @TraceID, 130, 49, @on
exec sp_trace_setevent @TraceID, 130, 6, @on
exec sp_trace_setevent @TraceID, 130, 10, @on
exec sp_trace_setevent @TraceID, 130, 14, @on
exec sp_trace_setevent @TraceID, 130, 26, @on
exec sp_trace_setevent @TraceID, 130, 34, @on
exec sp_trace_setevent @TraceID, 130, 42, @on
exec sp_trace_setevent @TraceID, 130, 50, @on
exec sp_trace_setevent @TraceID, 130, 3, @on
exec sp_trace_setevent @TraceID, 130, 11, @on
exec sp_trace_setevent @TraceID, 130, 35, @on
exec sp_trace_setevent @TraceID, 130, 43, @on
exec sp_trace_setevent @TraceID, 130, 51, @on
exec sp_trace_setevent @TraceID, 130, 4, @on
exec sp_trace_setevent @TraceID, 130, 12, @on
exec sp_trace_setevent @TraceID, 130, 28, @on
exec sp_trace_setevent @TraceID, 130, 60, @on
exec sp_trace_setevent @TraceID, 130, 21, @on
exec sp_trace_setevent @TraceID, 130, 37, @on
exec sp_trace_setevent @TraceID, 102, 7, @on
exec sp_trace_setevent @TraceID, 102, 23, @on
exec sp_trace_setevent @TraceID, 102, 39, @on
exec sp_trace_setevent @TraceID, 102, 8, @on
exec sp_trace_setevent @TraceID, 102, 40, @on
exec sp_trace_setevent @TraceID, 102, 64, @on
exec sp_trace_setevent @TraceID, 102, 1, @on
exec sp_trace_setevent @TraceID, 102, 9, @on
exec sp_trace_setevent @TraceID, 102, 41, @on
exec sp_trace_setevent @TraceID, 102, 49, @on
exec sp_trace_setevent @TraceID, 102, 10, @on
exec sp_trace_setevent @TraceID, 102, 26, @on
exec sp_trace_setevent @TraceID, 102, 34, @on
exec sp_trace_setevent @TraceID, 102, 42, @on
exec sp_trace_setevent @TraceID, 102, 50, @on
exec sp_trace_setevent @TraceID, 102, 3, @on
exec sp_trace_setevent @TraceID, 102, 11, @on
exec sp_trace_setevent @TraceID, 102, 19, @on
exec sp_trace_setevent @TraceID, 102, 35, @on
exec sp_trace_setevent @TraceID, 102, 43, @on
exec sp_trace_setevent @TraceID, 102, 51, @on
exec sp_trace_setevent @TraceID, 102, 4, @on
exec sp_trace_setevent @TraceID, 102, 12, @on
exec sp_trace_setevent @TraceID, 102, 28, @on
exec sp_trace_setevent @TraceID, 102, 60, @on
exec sp_trace_setevent @TraceID, 102, 21, @on
exec sp_trace_setevent @TraceID, 102, 37, @on
exec sp_trace_setevent @TraceID, 102, 6, @on
exec sp_trace_setevent @TraceID, 102, 14, @on
exec sp_trace_setevent @TraceID, 114, 7, @on
exec sp_trace_setevent @TraceID, 114, 23, @on
exec sp_trace_setevent @TraceID, 114, 8, @on
exec sp_trace_setevent @TraceID, 114, 40, @on
exec sp_trace_setevent @TraceID, 114, 64, @on
exec sp_trace_setevent @TraceID, 114, 1, @on
exec sp_trace_setevent @TraceID, 114, 9, @on
exec sp_trace_setevent @TraceID, 114, 41, @on
exec sp_trace_setevent @TraceID, 114, 49, @on
exec sp_trace_setevent @TraceID, 114, 10, @on
exec sp_trace_setevent @TraceID, 114, 26, @on
exec sp_trace_setevent @TraceID, 114, 34, @on
exec sp_trace_setevent @TraceID, 114, 50, @on
exec sp_trace_setevent @TraceID, 114, 3, @on
exec sp_trace_setevent @TraceID, 114, 11, @on
exec sp_trace_setevent @TraceID, 114, 19, @on
exec sp_trace_setevent @TraceID, 114, 35, @on
exec sp_trace_setevent @TraceID, 114, 51, @on
exec sp_trace_setevent @TraceID, 114, 4, @on
exec sp_trace_setevent @TraceID, 114, 12, @on
exec sp_trace_setevent @TraceID, 114, 28, @on
exec sp_trace_setevent @TraceID, 114, 44, @on
exec sp_trace_setevent @TraceID, 114, 60, @on
exec sp_trace_setevent @TraceID, 114, 21, @on
exec sp_trace_setevent @TraceID, 114, 37, @on
exec sp_trace_setevent @TraceID, 114, 6, @on
exec sp_trace_setevent @TraceID, 114, 14, @on
exec sp_trace_setevent @TraceID, 103, 7, @on
exec sp_trace_setevent @TraceID, 103, 23, @on
exec sp_trace_setevent @TraceID, 103, 39, @on
exec sp_trace_setevent @TraceID, 103, 8, @on
exec sp_trace_setevent @TraceID, 103, 40, @on
exec sp_trace_setevent @TraceID, 103, 64, @on
exec sp_trace_setevent @TraceID, 103, 1, @on
exec sp_trace_setevent @TraceID, 103, 9, @on
exec sp_trace_setevent @TraceID, 103, 41, @on
exec sp_trace_setevent @TraceID, 103, 49, @on
exec sp_trace_setevent @TraceID, 103, 10, @on
exec sp_trace_setevent @TraceID, 103, 26, @on
exec sp_trace_setevent @TraceID, 103, 34, @on
exec sp_trace_setevent @TraceID, 103, 42, @on
exec sp_trace_setevent @TraceID, 103, 50, @on
exec sp_trace_setevent @TraceID, 103, 3, @on
exec sp_trace_setevent @TraceID, 103, 11, @on
exec sp_trace_setevent @TraceID, 103, 19, @on
exec sp_trace_setevent @TraceID, 103, 35, @on
exec sp_trace_setevent @TraceID, 103, 43, @on
exec sp_trace_setevent @TraceID, 103, 51, @on
exec sp_trace_setevent @TraceID, 103, 4, @on
exec sp_trace_setevent @TraceID, 103, 12, @on
exec sp_trace_setevent @TraceID, 103, 28, @on
exec sp_trace_setevent @TraceID, 103, 44, @on
exec sp_trace_setevent @TraceID, 103, 60, @on
exec sp_trace_setevent @TraceID, 103, 21, @on
exec sp_trace_setevent @TraceID, 103, 37, @on
exec sp_trace_setevent @TraceID, 103, 6, @on
exec sp_trace_setevent @TraceID, 103, 14, @on
exec sp_trace_setevent @TraceID, 131, 7, @on
exec sp_trace_setevent @TraceID, 131, 23, @on
exec sp_trace_setevent @TraceID, 131, 8, @on
exec sp_trace_setevent @TraceID, 131, 40, @on
exec sp_trace_setevent @TraceID, 131, 64, @on
exec sp_trace_setevent @TraceID, 131, 1, @on
exec sp_trace_setevent @TraceID, 131, 41, @on
exec sp_trace_setevent @TraceID, 131, 49, @on
exec sp_trace_setevent @TraceID, 131, 6, @on
exec sp_trace_setevent @TraceID, 131, 10, @on
exec sp_trace_setevent @TraceID, 131, 14, @on
exec sp_trace_setevent @TraceID, 131, 26, @on
exec sp_trace_setevent @TraceID, 131, 34, @on
exec sp_trace_setevent @TraceID, 131, 50, @on
exec sp_trace_setevent @TraceID, 131, 3, @on
exec sp_trace_setevent @TraceID, 131, 11, @on
exec sp_trace_setevent @TraceID, 131, 35, @on
exec sp_trace_setevent @TraceID, 131, 51, @on
exec sp_trace_setevent @TraceID, 131, 4, @on
exec sp_trace_setevent @TraceID, 131, 12, @on
exec sp_trace_setevent @TraceID, 131, 28, @on
exec sp_trace_setevent @TraceID, 131, 60, @on
exec sp_trace_setevent @TraceID, 131, 21, @on
exec sp_trace_setevent @TraceID, 131, 37, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - c1848a6a-4b2f-4aa5-a967-b09c98662fdd'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 61d24ca0-b317-42d2-b10d-121925f4466d'
-- Tilbury MIS Staff Admin login Accounts
exec sp_trace_setfilter @TraceID, 11, 0, 1, N'MyDomain\AdminUser' -- The filter for my User account
exec sp_trace_setfilter @TraceID, 11, 0, 1, N'MyDomain\AdminUser1' -- User 2 that I want to log
exec sp_trace_setfilter @TraceID, 11, 0, 1, N'MyDomain\AdminUser2' -- User 3 that I want to log
exec sp_trace_setfilter @TraceID, 35, 0, 1, N'MyDatabaseName' -- <<<< filter for my database
exec sp_trace_setfilter @TraceID, 35, 0, 1, NULL
set @intfilter = 0
exec sp_trace_setfilter @TraceID, 60, 1, 0, @intfilter
-- 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
thanks
here is my script:
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
April 4, 2011 at 12:32 pm
dave-dj (4/4/2011)
Evening all,I'm trying to set up and SQL server trace using the create trace stored procedure. I generated the script in SQL Profiler and exported it.
In a nutshell what I want is to
a) filter for one specific database
b) filter for a couple of individuals
c) filter only security audit events
Dave you said you wanted to do the above, but that's NOT what your filters are setting: i ran this thru my "Script Any Trace" procedure, which gives human readable recap of the trace...it looks like you are excluding instead of including the specific users and specific database.
--filters
exec sp_trace_setfilter traceidout,10,0,7, N'SQL Server Profiler - c1848a6a-4b2f-4aa5-a967-b09c98662fdd' -- AND ApplicationName NOT LIKE SQL Server Profiler - c1848a6a-4b2f-4aa5-a967-b09c98662fdd
exec sp_trace_setfilter traceidout,10,0,7, N'SQL Server Profiler - 61d24ca0-b317-42d2-b10d-121925f4466d' -- AND ApplicationName NOT LIKE SQL Server Profiler - 61d24ca0-b317-42d2-b10d-121925f4466d
exec sp_trace_setfilter traceidout,11,0,1, N'MyDomain\AdminUser' -- AND LoginName <> MyDomain\AdminUser
exec sp_trace_setfilter traceidout,11,0,1, N'MyDomain\AdminUser1' -- AND LoginName <> MyDomain\AdminUser1
exec sp_trace_setfilter traceidout,11,0,1, N'MyDomain\AdminUser2' -- AND LoginName <> MyDomain\AdminUser2
exec sp_trace_setfilter traceidout,35,0,1, N'MyDatabaseName' -- AND DatabaseName <> MyDatabaseName
exec sp_trace_setfilter traceidout,35,0,1, NULL -- AND DatabaseName <> NULL
exec sp_trace_setfilter traceidout,60,1,0, N'0' -- OR IsSystem = 0
the comparision operators map to the following integers:
WHEN comparison_operator = 0 THEN ' = '
WHEN comparison_operator = 1 THEN ' <> '
WHEN comparison_operator = 2 THEN ' > '
WHEN comparison_operator = 3 THEN ' < '
WHEN comparison_operator = 4 THEN ' >= '
WHEN comparison_operator = 5 THEN ' <= '
WHEN comparison_operator = 6 THEN ' LIKE '
WHEN comparison_operator = 7 THEN ' NOT LIKE '
so i think you want yourt filters to be this:
-- Tilbury MIS Staff Admin login Accounts
exec sp_trace_setfilter @TraceID,10,0,7, N'SQL Server Profiler - c1848a6a-4b2f-4aa5-a967-b09c98662fdd' -- AND ApplicationName NOT LIKE SQL Server Profiler - c1848a6a-4b2f-4aa5-a967-b09c98662fdd
exec sp_trace_setfilter @TraceID,10,0,7, N'SQL Server Profiler - 61d24ca0-b317-42d2-b10d-121925f4466d' -- AND ApplicationName NOT LIKE SQL Server Profiler - 61d24ca0-b317-42d2-b10d-121925f4466d
exec sp_trace_setfilter @TraceID,11,0,0, N'MyDomain\AdminUser' -- AND LoginName = MyDomain\AdminUser
exec sp_trace_setfilter @TraceID,11,1,0, N'MyDomain\AdminUser1' -- OR LoginName = MyDomain\AdminUser1
exec sp_trace_setfilter @TraceID,11,1,0, N'MyDomain\AdminUser2' -- OR LoginName = MyDomain\AdminUser2
exec sp_trace_setfilter @TraceID,35,0,0, N'MyDatabaseName' -- AND DatabaseName = MyDatabaseName
Lowell
April 5, 2011 at 6:22 am
Dave did you get this settled and working?
Lowell
April 5, 2011 at 7:27 am
Hi Lowell,
Yes I did thank you. I appreciate the help.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply