April 12, 2016

Read Transaction logs for delete and drop records/objects


Someone is having fun deleting records and dropping objects on our development database, so, after adding some DDL triggers and also researching on the logs we found our culprit. So here is what was done on the logs...

----- Delete records
SELECT 
    [Transaction ID],
    Operation,
    Context,
    AllocUnitName
    
FROM 
    fn_dblog(NULL, NULL) 
WHERE 
    Operation = 'LOP_DELETE_ROWS'

go
--- Get transaction info
SELECT
    Operation,
    [Transaction ID],
    [Begin Time],
    [Transaction Name],
    [Transaction SID]
FROM
    fn_dblog(NULL, NULL)
WHERE
    [Transaction ID] = '0000:2a9ebb7c' --- TransactionId 
AND
    [Operation] = 'LOP_BEGIN_XACT'

go
Use Master;
SELECT SUSER_SNAME(0xABCC8713F5E4F04F95873B61C41D3CDA)--TransactionSid

go
----Drop table statement
SELECT 
Operation,
[Transaction Id],
[Transaction SID],
[Transaction Name],
 [Begin Time],
   [SPID],
   Description
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'
--
Use Master;
SELECT SUSER_SNAME(0xABCC8713F5E4F04F95873B61C41D3CDA)--TransactionSid

No comments:

Post a Comment

Contact Form

Name

Email *

Message *