How to access user information via sys.fn_dump_dblog

Question

I am reviewing sys.fn_dump_dblog and I can see the old states of the records, but I can not see which users are doing the operations. SPID, Transaction SID columns are always null, how to fix it, or any options?

I am using SQL Server 2016.

My query:

SELECT
    *
FROM sys.fn_dump_dblog(NULL, NULL, NULL, 1, 'E:\LogBackup\blabla.trn', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
WHERE (
[operation] IN ('LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW')
AND (
([context] IN ('LCX_HEAP', 'LCX_CLUSTERED', 'LCX_MARK_AS_GHOST'))
OR ([context] = 'LCX_TEXT_MIX'
AND (DATALENGTH([RowLog Contents 0]) IN (0, 14, 28)))
)
AND [PartitionID] IN (72057595034533888)
)
OR ([operation] = 'LOP_HOBT_DDL')

score 2 · Accepted Answer · answered Jul 16 '18 at 13:14

By reference to @Kin, I have come to the solve with the query.

SELECT
    SUSER_SNAME([Transaction SID]) UserName
    ,[Current LSN]
   ,[operation]
   ,[Context]
   ,[Transaction ID]
   ,[Transaction Name]
   ,[Begin Time]
   ,[End Time]
   ,[Flag Bits]
   ,[PartitionID]
   ,[Page ID]
   ,[Slot ID]
   ,[RowLog Contents 0]
   ,[Log Record]
   ,[RowLog Contents 1]
   INTO ##temp
FROM sys.fn_dump_dblog(NULL, NULL, NULL, 1, 'E:\balbla.trn', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
WHERE (([operation] IN ('LOP_BEGIN_XACT', 'LOP_COMMIT_XACT', 'LOP_ABORT_XACT'))
OR ([operation] IN ('LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW')
AND (([context] IN ('LCX_HEAP', 'LCX_CLUSTERED', 'LCX_MARK_AS_GHOST'))
OR ([context] = 'LCX_TEXT_MIX'
AND (DATALENGTH([RowLog Contents 0]) IN (0, 14, 28)))))
OR ([operation] = 'LOP_HOBT_DDL'))


SELECT t2.UserName,t.operation,object_name(p.object_id),count(1) FROM ##temp t
LEFT JOIN ##temp t2 ON t.[Transaction ID]=t2.[Transaction ID] AND t2.UserName is not null
INNER JOIN sys.partitions p ON p.partition_id=t.PartitionId
WHERE t.[operation] IN ('LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW')
GROUP BY t2.UserName,t.operation,object_name(p.object_id)

Randolph West · Answer 2 · 2018-07-13T19:25:46.903

The transaction log was not designed to keep track of who did what in your database. If you need to audit user behaviour on SQL Server, use SQL Server Auditing.

Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine. SQL Server audit lets you create server audits, which can contain server audit specifications for server level events, and database audit specifications for database level events. Audited events can be written to the event logs or to audit files.

You could use Extended Events as well, but as Nic points out in this answer, auditing is better suited to this scenario.

If you don't want to use auditing (to be clear, I recommend auditing because it will solve the problem you have without having to manually read the transaction log), you can create triggers on your tables to monitor user activity, but that is a LOT more work and is prone to bugs.

Use auditing.

How to access user information via sys.fn_dump_dblog

2 Answers2