Find out who DELETED RECORDS IN SQL SERVER Instantly

Are you also struggling in finding out the user who deleted database record in SQL Server? Look at the problem below:

Problem:

“Help!! One of my critical job get failed. The error message states that failure is because of missing table record. But how do I find out the person who did this? Is there any way to find out who deleted records in SQL Server. Also if there is any way to find out the last delete operation and last executed query performed in my table”

Calm! Here we will help you to vanish your struggle in finding out the user name who deleted records in SQL Server.

How to Find which user Deleted Records in SQL Server

There are number of ways through which you can find out who deleted records in SQL Server. Here, we will discuss the solution through SSMS, T-SQL and with a Quick approach. Let us first discuss the method using SSMS:

Identify the Person using T-SQL

You can find the user who deleted database records by running Transactional command . Copy this script:

To Load Trace File in SQL table:

By this, you will get all the details of your deleted database

Now run below script to find out who deleted records in SQL Server.

Identify who deleted database records in SQL Server – using SSMS

  1. Launch SSMS and Connect to SQL Instance
  2. Right click in the instance -- > Reports --> Standard Reports -->Schema Changes Report
  3. You will get pop up of ‘Schema Changes History’ which will show you the name of the user who deleted records in SQL Server.

Find which user Deleted Records in SQL Server – By fn_dblog function

You can find out the user who deleted records by fn_dblog function. You just need to run below command:

This will generate detail of transaction like Transaction Id, transaction SID and transaction name. Copy your transaction ID as generated and paste it along with SUSER_SNAME() function.

How to Find out who Deleted Records in SQL Server

Find out the Person who deleted records in SQL Server quickly by using SQL Log Viewer. SQL Log Analyzer provides you all detail of Transaction activities including the person name which has deleted record in your SQL Server database either intentionally or accidental.

Not only this, if you want to recover that data which someone has deleted, the tool will also do it in Simple recovery mode. You just need to perform following steps:

  1. Install and Launch SQL Log Viewer.
  2. Click on Open to load .ldf / .mdf file
  3. You can add file to the software by two different modes: Online Mode or Offline Mode.
  4. Choose accordingly. In Online mode, you just need to add Server details and the database name.
  5. In offline Mode, you need to browse the location of LDF & MDF File . Check the Get Login option. This will fetch the user name which has done changes to SQL server. And provide SQL Login credentials.
  6. The software will give you all details of Transaction activities. From here, you will be able to see the user who did changes to your SQL Server database.

Conclusion:

Worried who did changes to your SQL Server ? Want to caught that person! Find out who deleted records in SQL Server in different ways. What if you want to recover that database records also? Check out the automated solution which not only find which user Deleted Records in SQL Server but also recover those deleted records in SQL Server.

Previous
Next Post »

EmoticonEmoticon