January 12, 2007 at 9:24 am
I have auditing enabled on SQL Server using sp_trace_setevent and sp_trace_setfilter. I'm auditing high privileged accounts which doesn't include functional ids which have dbo access. This works fine on SQL Server 2000, however the filter doesn't seem to be working on SQL Server 2005.
Has anyone had the same issue? Is there any known difference between these procedures in the 2 versions of SQL Server.
TIA
January 12, 2007 at 12:21 pm
I don't see any difference between the two versions but are you trying modify the C2 audit trace?
MohammedU
Microsoft SQL Server MVP
January 12, 2007 at 2:06 pm
This is what am trying to do...
sp_trace_setfilter @TraceID, 38, 1, 6, sysadmin
For SQL 2000 this is filter all sysadmin acccount activity. But this is not working for SQL 2005.
January 13, 2007 at 9:38 pm
Is your columnid 38 is correct...?
I don't think we have 38 columns in sql trace...
Try scripting the trace script from profiler...
MohammedU
Microsoft SQL Server MVP
January 15, 2007 at 9:16 am
38 is Role Name, so should be the right one. There are 64 columns total, see sp_trace_setevent for a full list (or at least as full as I have found).
Have you set to log this column?
January 15, 2007 at 3:35 pm
Thanks Anders Pedersen... I was looking at the wrong place....
I think now I know why it is not working...
In sql 2005 all columns will not work with all events... so in this case COLUMN 38 is RoleName which works only with "Security Audi Event" and "Broker" events...
So make sure you choose the right events to work it out...
MohammedU
Microsoft SQL Server MVP
January 22, 2007 at 2:06 pm
Mohammed,
Is this behaviour different from SQL 2000? The traces I have work with SQL 2000.
January 22, 2007 at 6:30 pm
Based on my testing it is different and you can test too using 2005 profiler...
MohammedU
Microsoft SQL Server MVP
April 3, 2007 at 11:35 am
I tried a lot of different combinations, but haven't been able to figure this one out as yet. I have pasted the code below. The trace starts, file is created but the problem am having is that it is logging all activity and not just what's setup in the trace filter. Any ideas...
CREATE PROCEDURE dbo.audit_logins
@file_name nvarchar(155), -- NOTE: no file extentsion
@trace_id int output
AS
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 1000
exec @rc = sp_trace_create @TraceID output, 0, @file_name, @maxfilesize, NULL
if (@rc != 0)
RAISERROR ('Error with the sp_trace_create check to maked sure old file has been loaded and deleted', 16,1)
ELSE
BEGIN
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 7, @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, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 19, @on
exec sp_trace_setevent @TraceID, 12, 23, @on
exec sp_trace_setevent @TraceID, 12, 26, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 38, @on
exec sp_trace_setevent @TraceID, 12, 40, @on
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 3, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 7, @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, 13, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 15, @on
exec sp_trace_setevent @TraceID, 14, 16, @on
exec sp_trace_setevent @TraceID, 14, 17, @on
exec sp_trace_setevent @TraceID, 14, 18, @on
exec sp_trace_setevent @TraceID, 14, 19, @on
exec sp_trace_setevent @TraceID, 14, 23, @on
exec sp_trace_setevent @TraceID, 14, 26, @on
exec sp_trace_setevent @TraceID, 14, 35, @on
exec sp_trace_setevent @TraceID, 14, 38, @on
exec sp_trace_setevent @TraceID, 14, 40, @on
exec sp_trace_setevent @TraceID, 15, 1, @on
exec sp_trace_setevent @TraceID, 15, 3, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 7, @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, 13, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 19, @on
exec sp_trace_setevent @TraceID, 15, 23, @on
exec sp_trace_setevent @TraceID, 15, 26, @on
exec sp_trace_setevent @TraceID, 15, 35, @on
exec sp_trace_setevent @TraceID, 15, 38, @on
exec sp_trace_setevent @TraceID, 15, 40, @on
exec sp_trace_setevent @TraceID, 18, 1, @on
exec sp_trace_setevent @TraceID, 18, 3, @on
exec sp_trace_setevent @TraceID, 18, 6, @on
exec sp_trace_setevent @TraceID, 18, 7, @on
exec sp_trace_setevent @TraceID, 18, 8, @on
exec sp_trace_setevent @TraceID, 18, 9, @on
exec sp_trace_setevent @TraceID, 18, 10, @on
exec sp_trace_setevent @TraceID, 18, 11, @on
exec sp_trace_setevent @TraceID, 18, 12, @on
exec sp_trace_setevent @TraceID, 18, 13, @on
exec sp_trace_setevent @TraceID, 18, 14, @on
exec sp_trace_setevent @TraceID, 18, 15, @on
exec sp_trace_setevent @TraceID, 18, 16, @on
exec sp_trace_setevent @TraceID, 18, 17, @on
exec sp_trace_setevent @TraceID, 18, 18, @on
exec sp_trace_setevent @TraceID, 18, 19, @on
exec sp_trace_setevent @TraceID, 18, 23, @on
exec sp_trace_setevent @TraceID, 18, 26, @on
exec sp_trace_setevent @TraceID, 18, 35, @on
exec sp_trace_setevent @TraceID, 18, 38, @on
exec sp_trace_setevent @TraceID, 18, 40, @on
exec sp_trace_setevent @TraceID, 20, 1, @on
exec sp_trace_setevent @TraceID, 20, 3, @on
exec sp_trace_setevent @TraceID, 20, 6, @on
exec sp_trace_setevent @TraceID, 20, 7, @on
exec sp_trace_setevent @TraceID, 20, 8, @on
exec sp_trace_setevent @TraceID, 20, 9, @on
exec sp_trace_setevent @TraceID, 20, 10, @on
exec sp_trace_setevent @TraceID, 20, 11, @on
exec sp_trace_setevent @TraceID, 20, 12, @on
exec sp_trace_setevent @TraceID, 20, 13, @on
exec sp_trace_setevent @TraceID, 20, 14, @on
exec sp_trace_setevent @TraceID, 20, 15, @on
exec sp_trace_setevent @TraceID, 20, 16, @on
exec sp_trace_setevent @TraceID, 20, 17, @on
exec sp_trace_setevent @TraceID, 20, 18, @on
exec sp_trace_setevent @TraceID, 20, 19, @on
exec sp_trace_setevent @TraceID, 20, 23, @on
exec sp_trace_setevent @TraceID, 20, 26, @on
exec sp_trace_setevent @TraceID, 20, 35, @on
exec sp_trace_setevent @TraceID, 20, 38, @on
exec sp_trace_setevent @TraceID, 20, 40, @on
exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 3, @on
exec sp_trace_setevent @TraceID, 41, 6, @on
exec sp_trace_setevent @TraceID, 41, 7, @on
exec sp_trace_setevent @TraceID, 41, 8, @on
exec sp_trace_setevent @TraceID, 41, 9, @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, 19, @on
exec sp_trace_setevent @TraceID, 41, 23, @on
exec sp_trace_setevent @TraceID, 41, 26, @on
exec sp_trace_setevent @TraceID, 41, 35, @on
exec sp_trace_setevent @TraceID, 41, 38, @on
exec sp_trace_setevent @TraceID, 41, 40, @on
exec sp_trace_setevent @TraceID, 72, 1, @on
exec sp_trace_setevent @TraceID, 72, 3, @on
exec sp_trace_setevent @TraceID, 72, 6, @on
exec sp_trace_setevent @TraceID, 72, 7, @on
exec sp_trace_setevent @TraceID, 72, 8, @on
exec sp_trace_setevent @TraceID, 72, 9, @on
exec sp_trace_setevent @TraceID, 72, 10, @on
exec sp_trace_setevent @TraceID, 72, 11, @on
exec sp_trace_setevent @TraceID, 72, 12, @on
exec sp_trace_setevent @TraceID, 72, 13, @on
exec sp_trace_setevent @TraceID, 72, 14, @on
exec sp_trace_setevent @TraceID, 72, 15, @on
exec sp_trace_setevent @TraceID, 72, 16, @on
exec sp_trace_setevent @TraceID, 72, 17, @on
exec sp_trace_setevent @TraceID, 72, 18, @on
exec sp_trace_setevent @TraceID, 72, 19, @on
exec sp_trace_setevent @TraceID, 72, 23, @on
exec sp_trace_setevent @TraceID, 72, 26, @on
exec sp_trace_setevent @TraceID, 72, 35, @on
exec sp_trace_setevent @TraceID, 72, 38, @on
exec sp_trace_setevent @TraceID, 72, 40, @on
exec sp_trace_setevent @TraceID, 102, 1, @on
exec sp_trace_setevent @TraceID, 102, 3, @on
exec sp_trace_setevent @TraceID, 102, 6, @on
exec sp_trace_setevent @TraceID, 102, 7, @on
exec sp_trace_setevent @TraceID, 102, 8, @on
exec sp_trace_setevent @TraceID, 102, 9, @on
exec sp_trace_setevent @TraceID, 102, 10, @on
exec sp_trace_setevent @TraceID, 102, 11, @on
exec sp_trace_setevent @TraceID, 102, 12, @on
exec sp_trace_setevent @TraceID, 102, 13, @on
exec sp_trace_setevent @TraceID, 102, 14, @on
exec sp_trace_setevent @TraceID, 102, 15, @on
exec sp_trace_setevent @TraceID, 102, 16, @on
exec sp_trace_setevent @TraceID, 102, 17, @on
exec sp_trace_setevent @TraceID, 102, 18, @on
exec sp_trace_setevent @TraceID, 102, 19, @on
exec sp_trace_setevent @TraceID, 102, 23, @on
exec sp_trace_setevent @TraceID, 102, 26, @on
exec sp_trace_setevent @TraceID, 102, 35, @on
exec sp_trace_setevent @TraceID, 102, 38, @on
exec sp_trace_setevent @TraceID, 102, 40, @on
exec sp_trace_setevent @TraceID, 103, 1, @on
exec sp_trace_setevent @TraceID, 103, 3, @on
exec sp_trace_setevent @TraceID, 103, 6, @on
exec sp_trace_setevent @TraceID, 103, 7, @on
exec sp_trace_setevent @TraceID, 103, 8, @on
exec sp_trace_setevent @TraceID, 103, 9, @on
exec sp_trace_setevent @TraceID, 103, 10, @on
exec sp_trace_setevent @TraceID, 103, 11, @on
exec sp_trace_setevent @TraceID, 103, 12, @on
exec sp_trace_setevent @TraceID, 103, 13, @on
exec sp_trace_setevent @TraceID, 103, 14, @on
exec sp_trace_setevent @TraceID, 103, 15, @on
exec sp_trace_setevent @TraceID, 103, 16, @on
exec sp_trace_setevent @TraceID, 103, 17, @on
exec sp_trace_setevent @TraceID, 103, 18, @on
exec sp_trace_setevent @TraceID, 103, 19, @on
exec sp_trace_setevent @TraceID, 103, 23, @on
exec sp_trace_setevent @TraceID, 103, 26, @on
exec sp_trace_setevent @TraceID, 103, 35, @on
exec sp_trace_setevent @TraceID, 103, 38, @on
exec sp_trace_setevent @TraceID, 103, 40, @on
exec sp_trace_setevent @TraceID, 104, 1, @on
exec sp_trace_setevent @TraceID, 104, 3, @on
exec sp_trace_setevent @TraceID, 104, 6, @on
exec sp_trace_setevent @TraceID, 104, 7, @on
exec sp_trace_setevent @TraceID, 104, 8, @on
exec sp_trace_setevent @TraceID, 104, 9, @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, 13, @on
exec sp_trace_setevent @TraceID, 104, 14, @on
exec sp_trace_setevent @TraceID, 104, 15, @on
exec sp_trace_setevent @TraceID, 104, 16, @on
exec sp_trace_setevent @TraceID, 104, 17, @on
exec sp_trace_setevent @TraceID, 104, 18, @on
exec sp_trace_setevent @TraceID, 104, 19, @on
exec sp_trace_setevent @TraceID, 104, 23, @on
exec sp_trace_setevent @TraceID, 104, 26, @on
exec sp_trace_setevent @TraceID, 104, 35, @on
exec sp_trace_setevent @TraceID, 104, 38, @on
exec sp_trace_setevent @TraceID, 104, 40, @on
exec sp_trace_setevent @TraceID, 105, 1, @on
exec sp_trace_setevent @TraceID, 105, 3, @on
exec sp_trace_setevent @TraceID, 105, 6, @on
exec sp_trace_setevent @TraceID, 105, 7, @on
exec sp_trace_setevent @TraceID, 105, 8, @on
exec sp_trace_setevent @TraceID, 105, 9, @on
exec sp_trace_setevent @TraceID, 105, 10, @on
exec sp_trace_setevent @TraceID, 105, 11, @on
exec sp_trace_setevent @TraceID, 105, 12, @on
exec sp_trace_setevent @TraceID, 105, 13, @on
exec sp_trace_setevent @TraceID, 105, 14, @on
exec sp_trace_setevent @TraceID, 105, 15, @on
exec sp_trace_setevent @TraceID, 105, 16, @on
exec sp_trace_setevent @TraceID, 105, 17, @on
exec sp_trace_setevent @TraceID, 105, 18, @on
exec sp_trace_setevent @TraceID, 105, 19, @on
exec sp_trace_setevent @TraceID, 105, 23, @on
exec sp_trace_setevent @TraceID, 105, 26, @on
exec sp_trace_setevent @TraceID, 105, 35, @on
exec sp_trace_setevent @TraceID, 105, 38, @on
exec sp_trace_setevent @TraceID, 105, 40, @on
exec sp_trace_setevent @TraceID, 106, 1, @on
exec sp_trace_setevent @TraceID, 106, 3, @on
exec sp_trace_setevent @TraceID, 106, 6, @on
exec sp_trace_setevent @TraceID, 106, 7, @on
exec sp_trace_setevent @TraceID, 106, 8, @on
exec sp_trace_setevent @TraceID, 106, 9, @on
exec sp_trace_setevent @TraceID, 106, 10, @on
exec sp_trace_setevent @TraceID, 106, 11, @on
exec sp_trace_setevent @TraceID, 106, 12, @on
exec sp_trace_setevent @TraceID, 106, 13, @on
exec sp_trace_setevent @TraceID, 106, 14, @on
exec sp_trace_setevent @TraceID, 106, 15, @on
exec sp_trace_setevent @TraceID, 106, 16, @on
exec sp_trace_setevent @TraceID, 106, 17, @on
exec sp_trace_setevent @TraceID, 106, 18, @on
exec sp_trace_setevent @TraceID, 106, 19, @on
exec sp_trace_setevent @TraceID, 106, 23, @on
exec sp_trace_setevent @TraceID, 106, 26, @on
exec sp_trace_setevent @TraceID, 106, 35, @on
exec sp_trace_setevent @TraceID, 106, 38, @on
exec sp_trace_setevent @TraceID, 106, 40, @on
exec sp_trace_setevent @TraceID, 107, 1, @on
exec sp_trace_setevent @TraceID, 107, 3, @on
exec sp_trace_setevent @TraceID, 107, 6, @on
exec sp_trace_setevent @TraceID, 107, 7, @on
exec sp_trace_setevent @TraceID, 107, 8, @on
exec sp_trace_setevent @TraceID, 107, 9, @on
exec sp_trace_setevent @TraceID, 107, 10, @on
exec sp_trace_setevent @TraceID, 107, 11, @on
exec sp_trace_setevent @TraceID, 107, 12, @on
exec sp_trace_setevent @TraceID, 107, 13, @on
exec sp_trace_setevent @TraceID, 107, 14, @on
exec sp_trace_setevent @TraceID, 107, 15, @on
exec sp_trace_setevent @TraceID, 107, 16, @on
exec sp_trace_setevent @TraceID, 107, 17, @on
exec sp_trace_setevent @TraceID, 107, 18, @on
exec sp_trace_setevent @TraceID, 107, 19, @on
exec sp_trace_setevent @TraceID, 107, 23, @on
exec sp_trace_setevent @TraceID, 107, 26, @on
exec sp_trace_setevent @TraceID, 107, 35, @on
exec sp_trace_setevent @TraceID, 107, 38, @on
exec sp_trace_setevent @TraceID, 107, 40, @on
exec sp_trace_setevent @TraceID, 108, 1, @on
exec sp_trace_setevent @TraceID, 108, 3, @on
exec sp_trace_setevent @TraceID, 108, 6, @on
exec sp_trace_setevent @TraceID, 108, 7, @on
exec sp_trace_setevent @TraceID, 108, 8, @on
exec sp_trace_setevent @TraceID, 108, 9, @on
exec sp_trace_setevent @TraceID, 108, 10, @on
exec sp_trace_setevent @TraceID, 108, 11, @on
exec sp_trace_setevent @TraceID, 108, 12, @on
exec sp_trace_setevent @TraceID, 108, 13, @on
exec sp_trace_setevent @TraceID, 108, 14, @on
exec sp_trace_setevent @TraceID, 108, 15, @on
exec sp_trace_setevent @TraceID, 108, 16, @on
exec sp_trace_setevent @TraceID, 108, 17, @on
exec sp_trace_setevent @TraceID, 108, 18, @on
exec sp_trace_setevent @TraceID, 108, 19, @on
exec sp_trace_setevent @TraceID, 108, 23, @on
exec sp_trace_setevent @TraceID, 108, 26, @on
exec sp_trace_setevent @TraceID, 108, 35, @on
exec sp_trace_setevent @TraceID, 108, 38, @on
exec sp_trace_setevent @TraceID, 108, 40, @on
exec sp_trace_setevent @TraceID, 109, 1, @on
exec sp_trace_setevent @TraceID, 109, 3, @on
exec sp_trace_setevent @TraceID, 109, 6, @on
exec sp_trace_setevent @TraceID, 109, 7, @on
exec sp_trace_setevent @TraceID, 109, 8, @on
exec sp_trace_setevent @TraceID, 109, 9, @on
exec sp_trace_setevent @TraceID, 109, 10, @on
exec sp_trace_setevent @TraceID, 109, 11, @on
exec sp_trace_setevent @TraceID, 109, 12, @on
exec sp_trace_setevent @TraceID, 109, 13, @on
exec sp_trace_setevent @TraceID, 109, 14, @on
exec sp_trace_setevent @TraceID, 109, 15, @on
exec sp_trace_setevent @TraceID, 109, 16, @on
exec sp_trace_setevent @TraceID, 109, 17, @on
exec sp_trace_setevent @TraceID, 109, 18, @on
exec sp_trace_setevent @TraceID, 109, 19, @on
exec sp_trace_setevent @TraceID, 109, 23, @on
exec sp_trace_setevent @TraceID, 109, 26, @on
exec sp_trace_setevent @TraceID, 109, 35, @on
exec sp_trace_setevent @TraceID, 109, 38, @on
exec sp_trace_setevent @TraceID, 109, 40, @on
exec sp_trace_setevent @TraceID, 110, 1, @on
exec sp_trace_setevent @TraceID, 110, 3, @on
exec sp_trace_setevent @TraceID, 110, 6, @on
exec sp_trace_setevent @TraceID, 110, 7, @on
exec sp_trace_setevent @TraceID, 110, 8, @on
exec sp_trace_setevent @TraceID, 110, 9, @on
exec sp_trace_setevent @TraceID, 110, 10, @on
exec sp_trace_setevent @TraceID, 110, 11, @on
exec sp_trace_setevent @TraceID, 110, 12, @on
exec sp_trace_setevent @TraceID, 110, 13, @on
exec sp_trace_setevent @TraceID, 110, 14, @on
exec sp_trace_setevent @TraceID, 110, 15, @on
exec sp_trace_setevent @TraceID, 110, 16, @on
exec sp_trace_setevent @TraceID, 110, 17, @on
exec sp_trace_setevent @TraceID, 110, 18, @on
exec sp_trace_setevent @TraceID, 110, 19, @on
exec sp_trace_setevent @TraceID, 110, 23, @on
exec sp_trace_setevent @TraceID, 110, 26, @on
exec sp_trace_setevent @TraceID, 110, 35, @on
exec sp_trace_setevent @TraceID, 110, 38, @on
exec sp_trace_setevent @TraceID, 110, 40, @on
exec sp_trace_setevent @TraceID, 111, 1, @on
exec sp_trace_setevent @TraceID, 111, 3, @on
exec sp_trace_setevent @TraceID, 111, 6, @on
exec sp_trace_setevent @TraceID, 111, 7, @on
exec sp_trace_setevent @TraceID, 111, 8, @on
exec sp_trace_setevent @TraceID, 111, 9, @on
exec sp_trace_setevent @TraceID, 111, 10, @on
exec sp_trace_setevent @TraceID, 111, 11, @on
exec sp_trace_setevent @TraceID, 111, 12, @on
exec sp_trace_setevent @TraceID, 111, 13, @on
exec sp_trace_setevent @TraceID, 111, 14, @on
exec sp_trace_setevent @TraceID, 111, 15, @on
exec sp_trace_setevent @TraceID, 111, 16, @on
exec sp_trace_setevent @TraceID, 111, 17, @on
exec sp_trace_setevent @TraceID, 111, 18, @on
exec sp_trace_setevent @TraceID, 111, 19, @on
exec sp_trace_setevent @TraceID, 111, 23, @on
exec sp_trace_setevent @TraceID, 111, 26, @on
exec sp_trace_setevent @TraceID, 111, 35, @on
exec sp_trace_setevent @TraceID, 111, 38, @on
exec sp_trace_setevent @TraceID, 111, 40, @on
exec sp_trace_setevent @TraceID, 112, 1, @on
exec sp_trace_setevent @TraceID, 112, 3, @on
exec sp_trace_setevent @TraceID, 112, 6, @on
exec sp_trace_setevent @TraceID, 112, 7, @on
exec sp_trace_setevent @TraceID, 112, 8, @on
exec sp_trace_setevent @TraceID, 112, 9, @on
exec sp_trace_setevent @TraceID, 112, 10, @on
exec sp_trace_setevent @TraceID, 112, 11, @on
exec sp_trace_setevent @TraceID, 112, 12, @on
exec sp_trace_setevent @TraceID, 112, 13, @on
exec sp_trace_setevent @TraceID, 112, 14, @on
exec sp_trace_setevent @TraceID, 112, 15, @on
exec sp_trace_setevent @TraceID, 112, 16, @on
exec sp_trace_setevent @TraceID, 112, 17, @on
exec sp_trace_setevent @TraceID, 112, 18, @on
exec sp_trace_setevent @TraceID, 112, 19, @on
exec sp_trace_setevent @TraceID, 112, 23, @on
exec sp_trace_setevent @TraceID, 112, 26, @on
exec sp_trace_setevent @TraceID, 112, 35, @on
exec sp_trace_setevent @TraceID, 112, 38, @on
exec sp_trace_setevent @TraceID, 112, 40, @on
exec sp_trace_setevent @TraceID, 115, 1, @on
exec sp_trace_setevent @TraceID, 115, 3, @on
exec sp_trace_setevent @TraceID, 115, 6, @on
exec sp_trace_setevent @TraceID, 115, 7, @on
exec sp_trace_setevent @TraceID, 115, 8, @on
exec sp_trace_setevent @TraceID, 115, 9, @on
exec sp_trace_setevent @TraceID, 115, 10, @on
exec sp_trace_setevent @TraceID, 115, 11, @on
exec sp_trace_setevent @TraceID, 115, 12, @on
exec sp_trace_setevent @TraceID, 115, 13, @on
exec sp_trace_setevent @TraceID, 115, 14, @on
exec sp_trace_setevent @TraceID, 115, 15, @on
exec sp_trace_setevent @TraceID, 115, 16, @on
exec sp_trace_setevent @TraceID, 115, 17, @on
exec sp_trace_setevent @TraceID, 115, 18, @on
exec sp_trace_setevent @TraceID, 115, 19, @on
exec sp_trace_setevent @TraceID, 115, 23, @on
exec sp_trace_setevent @TraceID, 115, 26, @on
exec sp_trace_setevent @TraceID, 115, 35, @on
exec sp_trace_setevent @TraceID, 115, 38, @on
exec sp_trace_setevent @TraceID, 115, 40, @on
exec sp_trace_setevent @TraceID, 116, 1, @on
exec sp_trace_setevent @TraceID, 116, 3, @on
exec sp_trace_setevent @TraceID, 116, 6, @on
exec sp_trace_setevent @TraceID, 116, 7, @on
exec sp_trace_setevent @TraceID, 116, 8, @on
exec sp_trace_setevent @TraceID, 116, 9, @on
exec sp_trace_setevent @TraceID, 116, 10, @on
exec sp_trace_setevent @TraceID, 116, 11, @on
exec sp_trace_setevent @TraceID, 116, 12, @on
exec sp_trace_setevent @TraceID, 116, 13, @on
exec sp_trace_setevent @TraceID, 116, 14, @on
exec sp_trace_setevent @TraceID, 116, 15, @on
exec sp_trace_setevent @TraceID, 116, 16, @on
exec sp_trace_setevent @TraceID, 116, 17, @on
exec sp_trace_setevent @TraceID, 116, 18, @on
exec sp_trace_setevent @TraceID, 116, 19, @on
exec sp_trace_setevent @TraceID, 116, 23, @on
exec sp_trace_setevent @TraceID, 116, 26, @on
exec sp_trace_setevent @TraceID, 116, 35, @on
exec sp_trace_setevent @TraceID, 116, 38, @on
exec sp_trace_setevent @TraceID, 116, 40, @on
exec sp_trace_setevent @TraceID, 117, 1, @on
exec sp_trace_setevent @TraceID, 117, 3, @on
exec sp_trace_setevent @TraceID, 117, 6, @on
exec sp_trace_setevent @TraceID, 117, 7, @on
exec sp_trace_setevent @TraceID, 117, 8, @on
exec sp_trace_setevent @TraceID, 117, 9, @on
exec sp_trace_setevent @TraceID, 117, 10, @on
exec sp_trace_setevent @TraceID, 117, 11, @on
exec sp_trace_setevent @TraceID, 117, 12, @on
exec sp_trace_setevent @TraceID, 117, 13, @on
exec sp_trace_setevent @TraceID, 117, 14, @on
exec sp_trace_setevent @TraceID, 117, 15, @on
exec sp_trace_setevent @TraceID, 117, 16, @on
exec sp_trace_setevent @TraceID, 117, 17, @on
exec sp_trace_setevent @TraceID, 117, 18, @on
exec sp_trace_setevent @TraceID, 117, 19, @on
exec sp_trace_setevent @TraceID, 117, 23, @on
exec sp_trace_setevent @TraceID, 117, 26, @on
exec sp_trace_setevent @TraceID, 117, 35, @on
exec sp_trace_setevent @TraceID, 117, 38, @on
exec sp_trace_setevent @TraceID, 117, 40, @on
exec sp_trace_setevent @TraceID, 118, 1, @on
exec sp_trace_setevent @TraceID, 118, 3, @on
exec sp_trace_setevent @TraceID, 118, 6, @on
exec sp_trace_setevent @TraceID, 118, 7, @on
exec sp_trace_setevent @TraceID, 118, 8, @on
exec sp_trace_setevent @TraceID, 118, 9, @on
exec sp_trace_setevent @TraceID, 118, 10, @on
exec sp_trace_setevent @TraceID, 118, 11, @on
exec sp_trace_setevent @TraceID, 118, 12, @on
exec sp_trace_setevent @TraceID, 118, 13, @on
exec sp_trace_setevent @TraceID, 118, 14, @on
exec sp_trace_setevent @TraceID, 118, 15, @on
exec sp_trace_setevent @TraceID, 118, 16, @on
exec sp_trace_setevent @TraceID, 118, 17, @on
exec sp_trace_setevent @TraceID, 118, 18, @on
exec sp_trace_setevent @TraceID, 118, 19, @on
exec sp_trace_setevent @TraceID, 118, 23, @on
exec sp_trace_setevent @TraceID, 118, 26, @on
exec sp_trace_setevent @TraceID, 118, 35, @on
exec sp_trace_setevent @TraceID, 118, 38, @on
exec sp_trace_setevent @TraceID, 118, 40, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 11, 1, 6, N'sa'
exec sp_trace_setfilter @TraceID, 11, 1, 6, N'Domain\MA43212'
exec sp_trace_setfilter @TraceID, 11, 1, 6, N'Domain\SA12345'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
exec sp_trace_setfilter @TraceID, 38, 1, 6, N'sysadmin'
exec sp_trace_setfilter @TraceID, 38, 1, 6, N'dbcreator'
exec sp_trace_setfilter @TraceID, 38, 1, 6, N'bulkadmin'
exec sp_trace_setfilter @TraceID, 38, 1, 6, N'diskadmin'
exec sp_trace_setfilter @TraceID, 38, 1, 6, N'processadmin'
exec sp_trace_setfilter @TraceID, 38, 1, 6, N'securityadmin'
exec sp_trace_setfilter @TraceID, 38, 1, 6, N'serveradmin'
exec sp_trace_setfilter @TraceID, 38, 1, 6, N'setupadmin'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select @trace_id=@TraceID -- Return ID of the trace
END
GO
May 16, 2007 at 12:16 pm
Has anyone seen this URL. It says this is a bug in SQL 2005.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=258393
Does anyone know any work arounds?
May 16, 2007 at 1:40 pm
I reproduced the SA's link above test and I was getting "Expected Results" not bug results for SQL Server 2005 Standard Edition RTM and SQL Server 2005 Enterprise Edition SP2
Regards,Yelena Varsha
May 17, 2007 at 4:39 am
Can you post the events and filters? Do different event categories need to be created on different traces for this to work?
TIA
May 17, 2007 at 1:18 pm
Yelena,
If you see the trace script i have posted in the previous page, the problem is that the filters are not working as expected.
On SQL 2000, the trace picks up information for only those ids in the filter but on SQL 2005 it pulls in all the traces (kinda like c2 auditing).
May 18, 2007 at 11:05 am
SA,
I say AS Expected only in relation to the linked article that you posted. The article says it was OR instead of AND and it was an empty string instead of NULL, and I had expected behavior, not the actual one described in the article.
Yesterday I had to profile a certain user, and I had only this person's actions in Profiler.
Do as your article describes:
Open the first instance of the Profiler, set all events and filters in the UI - Profiler. Open the second instance of Profiler. Start the trace in the first instance and see the trace definition statements generated in the second instance.
Regards,Yelena Varsha
May 18, 2007 at 11:52 am
I got off the phone with Microsoft and this is a KNOWN BUG with SQL Server 2005 SP1. This has been fixed in SP2. The only change you may have to make is that the filter events need to be sorted in the column id field.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply