September 4, 2008 at 8:39 am
Is it possible to user SQL Profiler to capture insert, update, and delete statements?
I am trying to do this and doesn't seem to be working. I was just wondering if it is just not a capability avaliable with SQL Profiler or if it is a problem with my events/filters.
If it is not possible to capture inserts/updates/deletes, does anyone have a product they use to do this? I am trying to do this for a SOX application to monitor the System DBAs modifying data.
September 4, 2008 at 8:53 am
Actually, I am not having problems capturing the inserts/updates/deletes when performed in Query Analyzer. I am not able to capture them when performed within SQL Server Enterprise Manager. (Such as when I open a table and modify data that way)
--Event Class SQL:BatchCompleted (Occurs when a Transact-SQL batch has completed. ) Catches sql statements
exec sp_trace_setevent @TraceID, 12, 1, @on --TextData
exec sp_trace_setevent @TraceID, 12, 3, @on -- DatabaseID
exec sp_trace_setevent @TraceID, 12, 6, @on-- NTUserName
exec sp_trace_setevent @TraceID, 12, 8, @on-- ClientHostName
exec sp_trace_setevent @TraceID, 12, 10, @on-- Application Name
exec sp_trace_setevent @TraceID, 12, 11, @on--SQL Security Login Name
exec sp_trace_setevent @TraceID, 12, 12, @on--SPID
exec sp_trace_setevent @TraceID, 12, 14, @on--StartTime
exec sp_trace_setevent @TraceID, 12, 15, @on--EndTime
exec sp_trace_setevent @TraceID, 12, 22, @on--ObjectId
exec sp_trace_setevent @TraceID, 12, 23, @on--Success
exec sp_trace_setevent @TraceID, 12, 28, @on--ObjectType
exec sp_trace_setevent @TraceID, 12, 34, @on--ObjectName
-- Set the Filters from your script
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%CREATE %'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%ALTER %'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%DROP %'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%GRANT %'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%DENY %'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%REVOKE %'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%sp_addlogin%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%sp_droplogin%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%sp_grantlogin%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%sp_revokelogin%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%sp_denylogin%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%sp_addsrvrolemember%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%sp_dropsrvrolemember%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%sp_grantdbaccess%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%sp_revokeddbaccess%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%sp_adduser%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%sp_dropuser%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%sp_addrolemember%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%sp_droprolemember%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%sp_addrole%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%sp_droprole%'
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'%DELETE %'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%TRUNCATE TABLE %'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'%UPDATE STATISTICS %'
September 4, 2008 at 11:37 am
I figured it out.
I needed to include RPC:completed as an event.
exec sp_trace_setevent @TraceID, 10, 1, @on --TextData
exec sp_trace_setevent @TraceID, 10, 3, @on -- DatabaseID
exec sp_trace_setevent @TraceID, 10, 6, @on-- NTUserName
exec sp_trace_setevent @TraceID, 10, 8, @on-- ClientHostName
exec sp_trace_setevent @TraceID, 10, 10, @on-- Application Name
exec sp_trace_setevent @TraceID, 10, 11, @on--SQL Security Login Name
exec sp_trace_setevent @TraceID, 10, 10, @on--SPID
exec sp_trace_setevent @TraceID, 10, 14, @on--StartTime
exec sp_trace_setevent @TraceID, 10, 15, @on--EndTime
exec sp_trace_setevent @TraceID, 10, 22, @on--ObjectId
exec sp_trace_setevent @TraceID, 10, 23, @on--Success
exec sp_trace_setevent @TraceID, 10, 28, @on--ObjectType
exec sp_trace_setevent @TraceID, 10, 34, @on--ObjectName
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply