How to find all the activities for a particular login for last 30 days in one database ?

Question

How to find all the activities for a particular login for last 30 days in one database ?

Any particular script or tool ?

I prefer script.

---

Harry

Answer 1

Unless there's a rather sophisticated audit concept available that captures any data change as well as object modifications, there'll be little to no chance to get such information. Or do you have a server side trace for the last 30 days? This could be used, too.

Answer 2

Unless there's a rather sophisticated audit concept available that captures any data change as well as object modifications, there'll be little to no chance to get such information. Or do you have a server side trace for the last 30 days? This could be used, too.

To Harry:

Does "all activities" also include all read activities?

Answer 3

The question from Kent is very much valid.We need to know what all you want to track.

If its just logon events,then an Audit or trigger should work fine for you.

If you need to track reads/writes etc then you might think about a trace or even extended events for that matter.

---

Anup | Database Consultant

Blog: www.sqlsailor.com Twitter: Follow me !

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 4

If you are lucky enough to have the default blackbox trace running and retained for the last 30 days following might be helpful to you

select

* from fn_trace_getinfo(0) --details of trace running on the system

select

* from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_13.trc',default) -- example of the filename taken from the above query

where

LoginName like '%USER%'   --user name to be searched