SQL Server Default Trace

SQL Server Default Trace. Useful to tell who did what and when!

-- Get info about the default trace (includes the trace file location) ...

SELECT * FROM ::fn_trace_getinfo(default)

-- Show everything you can get from querying the default trace ...

SELECT t.EventID, t.ColumnID, e.name as Event_Description, c.name as Column_Description
FROM ::fn_trace_geteventinfo(1) t
INNER JOIN sys.trace_events e ON t.eventID = e.trace_event_id
INNER JOIN sys.trace_columns c ON t.columnid = c.trace_column_id

-- Query the default trace ...

SELECT 
loginname,
loginsid,
spid,
hostname,
applicationname,
servername,
databasename,
objectName,
e.category_id,
cat.name,
textdata,
starttime,
endtime,
duration,
eventclass,
eventsubclass,
e.name as EventName
FROM ::fn_trace_gettable('D:\Data\MSSQL10.MSSQLSERVER\MSSQL\Log\log_152.trc',0)
INNER JOIN sys.trace_events e ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id

Links :
There's Something about SQL! : Default Trace
MSDN : Default Trace