How to find out who deleted some data SQL Server

Question

My boss had a query from a customer yesterday asking how they could find out who deleted some data in their SQL Server database (it is the express edition if that matters).

I thought this could be found from the transaction log (providing it hadn't been truncated) - is this correct? And if so how do you actually go about finding this information out?

Answer 1

I've not tried fn_dblog on Express but if it is available the following will give you delete operations:

SELECT 
    * 
FROM 
    fn_dblog(NULL, NULL) 
WHERE 
    Operation = 'LOP_DELETE_ROWS'

Take the transaction ID for transactions you're interested in and identify the SID that initiated the transaction with:

SELECT
    [Transaction SID]
FROM
    fn_dblog(NULL, NULL)
WHERE
    [Transaction ID] = @TranID
AND
    [Operation] = 'LOP_BEGIN_XACT'

Then identify the user from the SID:

SELECT
    *
FROM 
    sysusers
WHERE
    [sid] = @SID

Edit: Bringing that all together to find deletes on a specified table:

DECLARE @TableName sysname
SET @TableName = 'dbo.Table_1'

SELECT
    u.[name] AS UserName
    , l.[Begin Time] AS TransactionStartTime
FROM
    fn_dblog(NULL, NULL) l
INNER JOIN
    (
    SELECT
        [Transaction ID]
    FROM 
        fn_dblog(NULL, NULL) 
    WHERE
        AllocUnitName LIKE @TableName + '%'
    AND
        Operation = 'LOP_DELETE_ROWS'
    ) deletes
ON  deletes.[Transaction ID] = l.[Transaction ID]
INNER JOIN
    sysusers u
ON  u.[sid] = l.[Transaction SID]