Programming

Who Deleted Rows from SQL Server

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.

If you get the result you are after, save it in case it is missing when you run it again!

SELECT TOP 10
    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 
    fn_dblog(NULL, NULL) LG
LEFT JOIN
    sysusers U ON U.[sid] = (
        SELECT
            ILOG.[Transaction SID]
        FROM
            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%'