SQL Server – How to Audit Rows Deleted Using SSMS GUI

sql serverssms

I found the following query from this question which appears to show row deletions from a specific table:

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

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]

The results all show the same username, which is the username we use from our app to connect to the database. However, I had just deleted a row using SSMS, while logged in using Windows Authentication. This record is not shown in the results from the query above.

How can I view an audit of rows/records deleted using SSMS (right-click, delete)?

Best Answer

In general you should use sys.database_principals instead of sysusers, which was deprecated in 2005 (just look at the big warning in the docs). But in this case, you can do this without joining to anything - SUSER_SNAME() against the sid retrieved the Windows username for me both writing a proper DELETE query and also when using the GUI as a crutch:

DECLARE @TableName sysname = N'dbo.ObjectInstances';

SELECT UserName = SUSER_SNAME(l.[Transaction SID]), l.[Begin Time]
FROM sys.fn_dblog(NULL, NULL) AS l
INNER JOIN
(
    SELECT [Transaction ID]
    FROM sys.fn_dblog(NULL, NULL) 
    WHERE AllocUnitName LIKE @TableName + N'%'
    AND Operation = N'LOP_DELETE_ROWS'
) AS deletes
ON  deletes.[Transaction ID] = l.[Transaction ID]
AND l.Operation = N'LOP_BEGIN_XACT';

You may also want to consider joining a 3rd time to be sure the sequence was finalized with a LOP_COMMIT_XACT (in other words, maybe you don't need to audit transactions that are still active or were rolled back, and this would also give you a better idea of when the change was actually committed vs. when they started thinking about making a change).

And just as an aside:

Related Question