SQL Server Log info for DELETE row(s) on a table

Question

Is there any way to know:

1. client username
2. client ip address
3. operation timestamp

of a row delete operation on some table in Microsoft SQL Server?

Answer 1

MS-SQL comes with some built in logging function for each database running on full recovery models (which is default).

You can try the following:

SELECT * FROM fn_dblog(NULL, NULL) 
WHERE Operation = 'LOP_DELETE_ROWS'

Then:

SELECT [Transaction SID]
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = @TranID
AND [Operation] = 'LOP_BEGIN_XACT'

And finally:

SELECT * FROM sysusers WHERE [sid] = @SID

---

Check out this article for further reading and additional abilities.

---

If this doesn't work - This means MS-SQL does not have the logging functions, and you would have to create them.

Luckily, It's simple enough - just follow these instructions.