Suppression accidentelle de ligne : comment retrouver le coupable ?

C’est vrai qu’il y a des outis, Change Data Capture et tout l’arsenal des Database Audit Specifications. Mais pour retrouver qui a supprimé les lignes dans la table T1 le 17 septembre dernier entre 12h00 et 14h00 il y a encore plus simple, pour peu qu’il y ait des backups de transactions.

La fonction qui tue:

Dans un article précédent, nous avons couvert une fonction table valuée fort intéressante qui permet de relire à l’intérieur d’une sauvegarde de journal: fn_dump_dblog(). Nous allons nous en servir pour connaître cette fois l’identité du coupable. La table concernée par le DELETE est la table T1, il faut commencer par récupérer sa valeur de allocation_unit_id:

select allocation_unit_id from sys.allocation_units AU inner join sys.partitions P
on P.hobt_id = AU.container_id where P.object_id = object_id('T1')

allocation_unit_id
---------------------
72057594043301888

Ensuite, rechercher dans les backups transactionnels des opérations de suppression de lignes sur cet objet:

select [Current LSN] ,Operation ,[Transaction ID] ,AllocUnitId
from fn_dump_dblog(DEFAULT, DEFAULT,DEFAULT, DEFAULT,
'C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery.TRAN2.bak',
DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT) where Operation  = 'LOP_DELETE_ROWS' and AllocUnitId = 72057594043301888

Current LSN             Operation          Transaction ID AllocUnitId         
----------------------- ------------------ -------------- --------------------
00000025:00000032:0003  LOP_DELETE_ROWS    0000:00000a9d  72057594043301888   
00000025:00000032:0005  LOP_DELETE_ROWS    0000:00000a9d  72057594043301888   
00000025:00000032:0006  LOP_DELETE_ROWS    0000:00000a9d  72057594043301888   
00000025:00000032:0007  LOP_DELETE_ROWS    0000:00000a9d  72057594043301888   
00000025:00000032:0008  LOP_DELETE_ROWS    0000:00000a9d  72057594043301888   
...

On récupère le LSN de la transaction pour retrouver le BEGIN TRAN correspondant:  0000:00000a9d:

select [Current LSN] ,Operation ,[Transaction ID] ,AllocUnitId
 ,[Begin Time] ,[Transaction Name] ,[End Time] ,[Description]
 ,[Transaction SID]
from fn_dump_dblog(DEFAULT, DEFAULT,DEFAULT, DEFAULT,
'C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery.TRAN2.bak',
DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
where Operation  = 'LOP_BEGIN_XACT' and [Transaction ID] = '0000:00000a9d'

Current LSN             Operation                       Transaction ID AllocUnitId        
----------------------- ------------------------------- -------------- --------------------
00000025:00000032:0001  LOP_BEGIN_XACT                  0000:00000a9d  NULL                

 Begin Time               Transaction Name                  Description                                  
------------------------ --------------------------------- -----------------------------------------------
2011/10/05 15:21:50:510  tralalala                         tralalala;0x75a770d73d6bf54bb0dc07725254ae57    

Transaction SID
------------------------------------
0x75A770D73D6BF54BB0DC07725254AE57

Remarque intéressante, [Transaction SID] (en rouge) correspond au SID de l’utilisateur dans la base…

Le dénouement…

Il ne reste plus qu’à dégainer la requête sur sys.database_principals:

select name from sys.database_principals where sid = 0x75A770D73D6BF54BB0DC07725254AE57

name
-------------------
user1

A+

Continuez votre lecture sur le blog :

• Point-in-time recovery et fn_dump_dblog() (David Baffaleuf) [SQL Server]
• Modes de récupération et journal de transactions, épisode 2 (David Baffaleuf) [SQL ServerVintage]
• Modes de récupération et journal de transactions, épisode 1 (David Baffaleuf) [SQL ServerVintage]
• Retrouver une transaction en échec (David Baffaleuf) [SQL ServerVintage]
• Error 8976 / 8978, problèmes de chaînage, comment récupérer les données (David Baffaleuf) [SQL Server]