Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Using ::fn_dblog() to find who deleted the rows in a table. Expand / Collapse
Posted Thursday, November 8, 2012 2:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 28, 2014 5:50 PM
Points: 37, Visits: 303
Thsi is a duplicate post. As this i srelated to security I posted it here too. Sorry for the inconvinenece.

Hi All,

Recently some one deleted some rows from a table. I was asked to find out who did it. Since the log has not been backed up since the time the DB was created I took the help of undocumented Table valued function ::fn_dblog() which gives me the contents of the active portion of the log.
I filtered on AlocUnitName and operation column.

Allocunitname being the table name and OPERATION being the 'LOP_DELETE_ROWS'.
I was looking fior the column TRANSACTION SID to find out the SID of the user that started the transaction that deleted the rows. I did get it.

But the problem is the value of the SID is 0x01 which is the dbo user. It is evident that a server level login with sysadmin privilages did the delets. Is there any way I can find out the server login mapped to the dbo user?

Any idea would be appriciated.
Post #1382719
Posted Thursday, November 8, 2012 2:53 PM



Group: General Forum Members
Last Login: Saturday, December 3, 2016 5:18 AM
Points: 45,619, Visits: 44,147

The transaction log is not an audit log, it is not important for database recovery or transaction rollbacks what login did the operation, that information is not included in the log at all.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1382727
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse