Audit user activity in SQL Server

Question

Please advice how can I audit all actions of specific user in SQL Server 2008 EE? If it can be traced and written to log file? If the audit has the levels?

With regards!

• McNets: Maybe this can help: bradmcgehee.com/2010/03/30/…

Answer 1

If i understand correctly, you want to see all activity from a single user.

The quickest would be a profiler trace filtered to that user, start a trace and in the Event Selection and Column Filters add the user under "Like" in the LoginName filter, or NTUserName if appropriate.

Example

Beware of overhead when using profiler in production it can be resource hungry.

If you want something more long term, you can look into auditing. Here is a useful tutorial for Select Statements but there is much more you can do with it.

Auditing Select Statements 2008

Answer 2

As sqlblog of jonathan_kehayias Here you can trace like who and from what machine a server side trace was created.

First execute the this TSQL query.

select 
 trace_event_id, 
 name
from sys.trace_events
where name like '%trace%';
Go

You shall get the output like here enter image description here

The run the next TSQL query like

DECLARE @FileName VARCHAR(MAX) 
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc' 
FROM sys.traces 
WHERE is_default = 1; 
SELECT 
 LoginName, 
 HostName, 
 ApplicationName, 
 StartTime, 
 TextData
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt 
WHERE EventClass = 175;
Go

This information is tracked in the Default Trace and can be found by querying for EventClass 175 which is the Audit Server Alter Trace Event trace_event_id from sys.trace_events. enter image description here