1

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!

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

-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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.