How to audit user activities from Transaction Log File

  • Article

There are scenarios where you may want to find out which user deleted the data or created a table etc .. 

 Use below statement in the database whose transaction log file is to be audited

DBCC LOG (<DBID\DBNAME>, 3)

Replace DBID with the ID of database whose transaction log file is to be analyzed.

This may generate huge output if the transaction log file size is big

You may get lucky if the transaction log is not truncated 

Next, in the output from above query you can look for TRANSACTION SID or DESCRIPTION column for point in time LSN having LOP_BEGIN_XACT statement.

Those column usually contain the SID value for the user who made the change & can be mapped in SID column in SYSUSERS table of that database to which the log file belongs.

Also check the TRANSACTION NAME column which may contain type of operation done, as a matter of fact the DESCRIPTION column usually contains operation & SID details. For e.g.

CREATE TABLE;0x0105000000000005150000005d28f57fd53ad8354354e02a00d40400