Using ::fn_dblog() to find who deleted the rows in a table.

  • 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.

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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, 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
  • 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, 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
  • 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.

  • 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, 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
  • 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 SPIDAllocunitnamename

    LOP_BEGIN_XACT0000:00000207DELETE;0x01 55NULL sa

    LOP_BEGIN_XACT0000:00000215DELETE;0xdd56d0e1cfe9fd42bafe0aac916518eb55NULL testlogin

    LOP_BEGIN_XACT0000:00000221DELETE;0x80f4a1243a4e6e439fffe00be23c086a55NULL test

    This worked for me.

    Thanks,

    Krishna

  • 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.

  • 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.

  • 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.

  • Thank you very much for the advice.

    Actualy We have both CDC as well as Auditing in place for the prod database. But this was a local environment. Where we have many sysadmins. I know it is a worst practice. I am new here and I adviced them not to. But they want it to stay this way.

    Regards

  • krishnarajeesh (12/23/2012)


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

    No, it won't. It has the database user info, not the login info.

    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
  • dedicatedtosql (12/26/2012)


    Thank you very much for the advice.

    Actualy We have both CDC as well as Auditing in place for the prod database. But this was a local environment. Where we have many sysadmins. I know it is a worst practice. I am new here and I adviced them not to. But they want it to stay this way.

    Regards

    Looks to me like you need to set up auditing and CDC in this environment as well.

  • If you have default trace records from around the time of the delete, you may be able to compile a list of suspects. Hopefully you do not too may people that have sysadmin access on your system.

  • arnipetursson (12/28/2012)


    If you have default trace records from around the time of the delete, you may be able to compile a list of suspects. Hopefully you do not too may people that have sysadmin access on your system.

    That won't help, I'm afraid.

    the default trace captured DDL changes..CREATE TABLE/INDEX etc kinds of things.

    it does not capture any DML statements like INSERT/UPDATE/DELETE; for that you need a different custom trace set up prior to the changes occurring to get any relevant info from any trace.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply