Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using ::fn_dblog() to find who deleted the rows in a table. Expand / Collapse
Author
Message
Posted Thursday, November 8, 2012 2:09 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
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 #1382711
Posted Thursday, November 8, 2012 3:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 13,007, Visits: 12,426
dedicatedtosql (11/8/2012)
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.


0x01 is always SA. Not going to provide much in the way of help there I'm afraid.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1382732
Posted Thursday, November 8, 2012 3:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 39,979, Visits: 36,342
0x01 as a user sid is DBO, that's the user mapped to all sysadmin logins, sa and any other member of the sysadmin role. The log does not contain login sids, just database user sids.




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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 #1382733
Posted Thursday, November 8, 2012 3:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 39,979, Visits: 36,342
Please don't cross post. It just results in people answering already answered questions.

Alspo asked at http://www.sqlservercentral.com/Forums/Topic1382719-1526-1.aspx



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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 #1382735
Posted Thursday, November 8, 2012 3:19 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
I am sorry for the repost. I will make point that I will not do it future. The reason I did that was since it was security question I wanted to do there as well.

So coming to the issue there is no way to track thye dbo back to thr login with sysadmin privilages right? No other column returned by ::fn_dblog() helps in tracking it back.

Any way thanks for the help.

Post #1382742
Posted Thursday, November 8, 2012 3:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 39,979, Visits: 36,342
Nope. All that's in the log is the user id. The transaction log is not an audit log. Rollbacks and database recovery do not require any information on the login, host, app or any other such information.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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 #1382747
Posted Sunday, December 23, 2012 8:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 12:56 AM
Points: 10, Visits: 785
Just try searching for [Transaction Name] LIKE '%delete%'.

That is OPERATION 'LOP_DELETE_ROWS' will not have have the login info, where as "LOP_BEGIN_XACT" for that delete will have.

Sample query

SELECT
[Current LSN],
[Operation],
[Transaction ID],
[Description], SPID,[Begin Time], [Transaction SID],
name 'LoginName'
FROM fn_dblog (NULL, NULL),
(select sid,name from sys.syslogins) sl
where [Transaction Name] LIKE '%delete%' and [Transaction SID] = sl.sid

Operation Transaction ID Description SPID Allocunitname name
LOP_BEGIN_XACT 0000:00000207 DELETE;0x01 55 NULL sa
LOP_BEGIN_XACT 0000:00000215 DELETE;0xdd56d0e1cfe9fd42bafe0aac916518eb 55 NULL testlogin
LOP_BEGIN_XACT 0000:00000221 DELETE;0x80f4a1243a4e6e439fffe00be23c086a 55 NULL test

This worked for me.

Thanks,
Krishna
Post #1399788
Posted Tuesday, December 25, 2012 5:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 28, 2014 11:20 PM
Points: 6, Visits: 138
SELECT
[Current LSN],
[Operation],
[Transaction ID],
[Description], SPID,[Begin Time], [Transaction SID],
name 'LoginName'
FROM fn_dblog (NULL, NULL),
(select sid,name from sys.syslogins) sl
where [Transaction Name] LIKE '%delete%' and [Transaction SID] = sl.sid



this query is not showing any results though the rows got deleted from the table. I have few rows from the table and checked it. Its not giving any results with details who has deleted them.
Can you please help on this.
Post #1400108
Posted Wednesday, December 26, 2012 5:39 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
Yeah I did the same thing. But the problem here is the SID was showing 0x01 which is a dbo user. i.e.. a sysadmin mapped to dbo with have SID 0x01. SO it is has not possible(to my knowledge) to get which login performed the delete.

Post #1400421
Posted Wednesday, December 26, 2012 5:41 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:58 PM
Points: 20,705, Visits: 32,356
If it is important to know who did something, you should look at setting up auditing so that you can capture this in the future. You may also want to look at CDC if you need to capture the actual changes to data.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1400422
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse