Skip to main content

Want to know who deleted rows from your SQL database? The script below joins up delete transactions with users in order to find out who just deleted something. Because this comes from the transaction log, you'll only find stuff that is still available in there. This means it will be affected by retention/recovery modes. If you know something just got deleted, it can be pretty useful.

select top (100) U.name as UserName,
    LG.Operation,
    LG.AllocUnitName,
    LG.[RowLog Contents 0],
    LG.[RowLog Contents 1],
    LG.[RowLog Contents 2],
    LG.[RowLog Contents 3],
    LG.[RowLog Contents 4],
    LG.[RowLog Contents 5],
    LG.[Log Record]
from sys.fn_dblog(null, null) as LG
left join sys.sysusers as U
    on U.sid = (
        select ILOG.[Transaction SID]
            from sys.fn_dblog(null, null) as ILOG
            where ILOG.[Transaction ID] = LG.[Transaction ID]
                and ILOG.Operation = 'LOP_BEGIN_XACT'
    )
where LG.Operation = 'LOP_DELETE_ROWS'
    -- For a particular user
    --and U.name = 'John.Doe'
    -- For a particular table
    --and LG.AllocUnitName like '%dbo.TableName%';