Auditar linhas excluídas usando SSMS GUI?

Aug 31 2020

Encontrei a seguinte consulta desta pergunta que parece mostrar exclusões de linhas de uma tabela específica:

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]

Todos os resultados mostram o mesmo nome de usuário, que é o nome de usuário que usamos em nosso aplicativo para nos conectar ao banco de dados. No entanto, acabei de excluir uma linha usando SSMS, enquanto logava usando a autenticação do Windows. Este registro não é mostrado nos resultados da consulta acima.

Como posso visualizar uma auditoria de linhas / registros excluídos usando SSMS (clique com o botão direito, excluir)?

Respostas

2 AaronBertrand Aug 31 2020 at 21:08

Em geral, você deve usar em sys.database_principalsvez de sysusers, que foi descontinuado em 2005 (basta olhar o grande aviso nos documentos ). Mas, neste caso, você pode fazer isso sem se juntar a nada - SUSER_SNAME()contra o sid recuperou o nome de usuário do Windows para mim, tanto ao escrever uma DELETEconsulta adequada quanto ao usar a GUI como uma muleta:

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';

Você também pode querer participar de uma terceira vez para ter certeza de que a sequência foi finalizada com um LOP_COMMIT_XACT(em outras palavras, talvez você não precise auditar transações que ainda estão ativas ou foram revertidas, e isso também lhe daria uma melhor ideia de quando a mudança foi realmente cometida vs. quando eles começaram a pensar em fazer uma mudança).

E apenas como um aparte:

  • Senhoras e senhores, comecem seus pontos-e-vírgulas!