Auditar linhas excluídas usando SSMS GUI?
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
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!