dcsimg
LinkedIn
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Tip: Get a User that has Deleted Rows with SQL

Finding a user who has deleted rows is a multi-part process. Get some tips on how to get it done.


advertisement

Getting a User that has deleted rows with SQL is a multi-part process.

First you have to search the transaction log file for information on deleted rows.

SELECT 
    [Transaction ID],
    Operation,
    Context,
    AllocUnitName
    
FROM 
    fn_dblog(NULL, NULL) 
WHERE 
    Operation = 'LOP_DELETE_ROWS' --Get Deleted Rows & Transaction ID

This will show you the Deleted Rows and give a Transaction ID for each operation. Now you need to use this Transaction ID to get the transaction SID of the user who has deleted the data:

SELECT 
    [Transaction ID],
    Operation,
    Context,
    AllocUnitName
    
FROM 
    fn_dblog(NULL, NULL) 
WHERE 
		[Transaction ID] = '0000:0000035' --Example Transaction ID to Delete
	AND
	    Operation = 'LOP_BEGIN_XACT' --GET Transaction SID

Finally, you need to convert the Transaction SID into readable text to display the user details:

SELECT SUSER_SNAME(0x0105000000000002120000009F11CA398C81F81398D0CD14E8030030) --Based on Transaction SID That Was Returned Earlier

 

Visit the DevX Tip Bank

 



   
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date