Início / dba / Perguntas / 211729
Accepted
Yunus UYANIK
Asked: 2018-07-10 23:20:52 +0800 CST

Como acessar as informações do usuário via sys.fn_dump_dblog

  • 772

Estou revisando sys.fn_dump_dblog e posso ver os estados antigos dos registros, mas não consigo ver quais usuários estão fazendo as operações. As colunas SPID, Transaction SID são sempre nulas, como corrigi-lo ou alguma opção?

Estou usando o SQL Server 2016.

Minha consulta:

SELECT
    *
FROM sys.fn_dump_dblog(NULL, NULL, NULL, 1, 'E:\LogBackup\blabla.trn', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
WHERE (
[operation] IN ('LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW')
AND (
([context] IN ('LCX_HEAP', 'LCX_CLUSTERED', 'LCX_MARK_AS_GHOST'))
OR ([context] = 'LCX_TEXT_MIX'
AND (DATALENGTH([RowLog Contents 0]) IN (0, 14, 28)))
)
AND [PartitionID] IN (72057595034533888)
)
OR ([operation] = 'LOP_HOBT_DDL')
sql-server transaction-log

2 respostas

  1. Best Answer

    Por referência a @Kin, cheguei à solução com a consulta.

    SELECT
    SUSER_SNAME([Transaction SID]) UserName
    ,[Current LSN]
   ,[operation]
   ,[Context]
   ,[Transaction ID]
   ,[Transaction Name]
   ,[Begin Time]
   ,[End Time]
   ,[Flag Bits]
   ,[PartitionID]
   ,[Page ID]
   ,[Slot ID]
   ,[RowLog Contents 0]
   ,[Log Record]
   ,[RowLog Contents 1]
   INTO ##temp
FROM sys.fn_dump_dblog(NULL, NULL, NULL, 1, 'E:\balbla.trn', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
WHERE (([operation] IN ('LOP_BEGIN_XACT', 'LOP_COMMIT_XACT', 'LOP_ABORT_XACT'))
OR ([operation] IN ('LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW')
AND (([context] IN ('LCX_HEAP', 'LCX_CLUSTERED', 'LCX_MARK_AS_GHOST'))
OR ([context] = 'LCX_TEXT_MIX'
AND (DATALENGTH([RowLog Contents 0]) IN (0, 14, 28)))))
OR ([operation] = 'LOP_HOBT_DDL'))


SELECT t2.UserName,t.operation,object_name(p.object_id),count(1) FROM ##temp t
LEFT JOIN ##temp t2 ON t.[Transaction ID]=t2.[Transaction ID] AND t2.UserName is not null
INNER JOIN sys.partitions p ON p.partition_id=t.PartitionId
WHERE t.[operation] IN ('LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW')
GROUP BY t2.UserName,t.operation,object_name(p.object_id)
    • 2

  2. O log de transações não foi projetado para acompanhar quem fez o quê em seu banco de dados. Se você precisar auditar o comportamento do usuário no SQL Server, use o SQL Server Auditing .

    A auditoria de uma instância do Mecanismo de Banco de Dados do SQL Server ou de um banco de dados individual envolve rastrear e registrar eventos que ocorrem no Mecanismo de Banco de Dados. A auditoria do SQL Server permite criar auditorias de servidor, que podem conter especificações de auditoria de servidor para eventos de nível de servidor e especificações de auditoria de banco de dados para eventos de nível de banco de dados. Os eventos auditados podem ser gravados nos logs de eventos ou em arquivos de auditoria.

    Você também pode usar eventos estendidos, mas, como Nic aponta nesta resposta , a auditoria é mais adequada para esse cenário.

    Se você não quiser usar auditoria (para ser claro, recomendo auditar porque resolverá o problema que você tem sem ter que ler manualmente o log de transações), você pode criar gatilhos em suas tabelas para monitorar a atividade do usuário, mas isso é muito mais trabalho e é propenso a bugs.

    Usar auditoria.

    • 0

relate perguntas