Wednesday, 21 August 2024

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

No comments:

Post a Comment

If you have any doubt, please let me know.

Popular Posts