dcsimg
Login | Register   
LinkedIn
Google+
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

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


Tip of the Day
Language: SQL
Expertise: Intermediate
Jul 27, 2017

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


Get a User that has Deleted Rows with SQL

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
Hannes du Preez
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date