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.