SQL know what queries was executed

Question

Is possible to get information in SQL Server 2008 what user executed what query? If somebody change for example view, the information is save in DDL_Log.

I don't have audit on one table and i must know what user exec what queries, is it possible to know ?

I must know for example the user X do

SELECt id FROM Person

or for example user Y do

UPDATE TOP(1) Person SET Name = 'John' WHERE id = 6

Answer 1

Consider using sp_trace_xxx procedures to capture activity:

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @traceoptions int
declare @stoptime datetime
set @traceoptions = 2
set @maxfilesize = 999  
set @stoptime = '2011-12-10 19:20:00'

exec @rc = sp_trace_create @TraceID output, 0, N'd:\sql server\deftrace', @maxfilesize, @stoptime

if (@rc != 0) goto error

-- Set the events
declare @on bit
set @on = 1
-- SQL:BatchCompleted events
exec sp_trace_setevent @TraceID, 12, 1, @on -- textdata
exec sp_trace_setevent @TraceID, 12, 9, @on -- client process id
exec sp_trace_setevent @TraceID, 12, 10, @on -- application name
exec sp_trace_setevent @TraceID, 12, 11, @on -- login name
exec sp_trace_setevent @TraceID, 12, 12, @on -- spid
exec sp_trace_setevent @TraceID, 12, 13, @on -- duration (usec)
exec sp_trace_setevent @TraceID, 12, 14, @on -- start time
exec sp_trace_setevent @TraceID, 12, 15, @on -- end time
exec sp_trace_setevent @TraceID, 12, 16, @on -- reads
exec sp_trace_setevent @TraceID, 12, 17, @on -- writes
exec sp_trace_setevent @TraceID, 12, 18, @on -- cpu
exec sp_trace_setevent @TraceID, 12, 34, @on -- object name
exec sp_trace_setevent @TraceID, 12, 35, @on -- database name

-- RPC:Completed events
exec sp_trace_setevent @TraceID, 10, 1, @on -- textdata
exec sp_trace_setevent @TraceID, 10, 9, @on -- client process id
exec sp_trace_setevent @TraceID, 10, 10, @on -- application name
exec sp_trace_setevent @TraceID, 10, 11, @on -- login name
exec sp_trace_setevent @TraceID, 10, 12, @on -- spid
exec sp_trace_setevent @TraceID, 10, 13, @on -- duration (usec)
exec sp_trace_setevent @TraceID, 10, 14, @on -- start time
exec sp_trace_setevent @TraceID, 10, 15, @on -- end time
exec sp_trace_setevent @TraceID, 10, 16, @on -- reads
exec sp_trace_setevent @TraceID, 10, 17, @on -- writes
exec sp_trace_setevent @TraceID, 10, 18, @on -- cpu
exec sp_trace_setevent @TraceID, 10, 34, @on -- object name
exec sp_trace_setevent @TraceID, 10, 35, @on -- database name

-- Filter out the SQL Profiler events
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'

-- 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

Then run:

exec sp_trace_setstatus @TraceID, 0
exec sp_trace_setstatus @TraceID, 2

To stop the trace (or wait till @stoptime). To review executed queries, run:

SELECT DatabaseName, TextData, Reads, Writes, CPU, SPID, Duration / 1000 Duration, StartTime, EndTime, ClientProcessID, LoginName, ApplicationName
FROM fn_trace_gettable('d:\sql server\deftrace.trc', default);

Those code samples are useful for diagnostics and query profiling. With a bit of tuning, you can use them to just log commands, users and timestamps.