November 1, 2011 at 4:09 pm
We would like to audit user's activity in SQL Server. I am looking for any solution including 3rd party tools gladly appreciate the help on this. Almost forgot to mention that I do not want to user SQL Profiler for this. Thank you.
November 2, 2011 at 8:06 am
I use a combination of native SQL Server 2008 Audit and CDC for this in my environment. Before switching from '05 to '08, I assessed several third-party tools, but found that none of them met my needs.
November 2, 2011 at 8:22 am
Your best bet is going to be a server side trace. You can use Profiler to build the trace. It's the only way to capture what all is being done. There are ways of obfuscating operations with the Audit object. A server side trace will typically catch most of these.
K. Brian Kelley
@kbriankelley
November 2, 2011 at 8:31 am
I uses server-side traces for this.
Either use Profiler to script the trace, like K. Brian Kelley suggested, or use sp_trace_create and sp_trace_setevent (which is what I do).
Here's a sample trace script (I use this as a template, and refine it from there for the specific needs of the trace I'm building):
declare @ID int, @FS bigint, @FilePath nvarchar(245), @1 bit
select @FS = 100,
@filepath = 'C:\DBATrace\TSQLTrace',
@1 = 1
exec sp_trace_create
@traceid = @id output, -- Trace ID (output)
@options = 2, -- File rollover option
@tracefile = @filepath, -- Must be a local path on the server
@maxfilesize = @FS, -- Trace File Size (in Meg)
@stoptime = null, -- No predetermined stop time
@filecount = 10 -- Max number of trace files (rolls over)
-- 10 = RPC Complete
-- Sets columns for trace data
exec sp_trace_setevent @id, 10, 1, @1
exec sp_trace_setevent @id, 10, 3, @1
exec sp_trace_setevent @id, 10, 13, @1
exec sp_trace_setevent @id, 10, 15, @1
exec sp_trace_setevent @id, 10, 16, @1
exec sp_trace_setevent @id, 10, 17, @1
exec sp_trace_setevent @id, 10, 18, @1
-- 12 = SQL Batch Complete
-- Sets columns for trace data
exec sp_trace_setevent @id, 12, 1, @1
exec sp_trace_setevent @id, 12, 3, @1
exec sp_trace_setevent @id, 12, 13, @1
exec sp_trace_setevent @id, 12, 15, @1
exec sp_trace_setevent @id, 12, 16, @1
exec sp_trace_setevent @id, 12, 17, @1
exec sp_trace_setevent @id, 12, 18, @1
-- Which database to trace (if not set, will trace all)
exec sp_trace_setfilter @id, 35, 0, 0, N'OCDATA'
-- Turns on the trace
exec sp_trace_setstatus @id, 1
-- Trace summary data (numeric codes, not particularly human-readable)
select *
from fn_trace_getinfo(@id)
--exec sp_trace_setstatus 2, 0 -- Stop the trace
--exec sp_trace_setstatus 2, 2 -- Delete the trace
--exec sp_trace_setstatus 2, 1 -- Restart the trace
--select * from fn_trace_getinfo(0) -- Select data on all active traces
--select * from fn_trace_gettable ( filename , number_files )
-- To select data from the trace while running.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply