Find users who ran UPDATE or DELETE statements in SQL Server

Its commonly happened at all work place where a user trigger a UPDATE or DELETE command by mistake or intensely on a SQL Server and no one will be accepting who did this. Here, I would like to demonstrate a way using the transaction log to track down helpful information related to this incident.

We can use undocumented function fn_dblog to find any unauthorized or unapproved deletes or updates.

Create a table and inserting some records into this table.

CREATE TABLE customer   (      cus_name VARCHAR(50),      cus_add  VARCHAR(100),      mob      VARCHAR(10)   )  insert into customer(cus_name,cus_add,mob) values ('Bagesh','noida','888880XXXX'); insert into customer(cus_name,cus_add,mob) values ('Rajesh','Pune','88XXX0XXXX'); insert into customer(cus_name,cus_add,mob) values ('Mahesh','Chennai','ZZZ880XXXX'); insert into customer(cus_name,cus_add,mob) values ('Mohan','Patna','888880DDDD');

Table created and data inserted into the table. 

When we are inserting the records in the table it is logged in the transaction log.

Let’s update 1 records.

update customer set cus_name='Bagesh Kumar Singh' where cus_name='Bagesh'

See the log.

Now the question is that who has updated the records.

SELECT     Operation,        SUSER_SNAME([Transaction SID]),     [Transaction ID],     [Begin Time],     [Transaction Name] FROM     fn_dblog(NULL, NULL) WHERE  [Operation] = 'LOP_BEGIN_XACT'        and [Transaction Name]='UPDATE'

   

Let’s delete the all records from the table. 

Records deleted successfully. 

Similarly if we drop the table we will get the information

Let’s drop the table.

See the log.

Thanks! J