SQL Server 2014 Hosting – How to Track User That Dropped/Deleted TSQL on SQL Server Objects

Has somebody dropped or deleted objects from your database, but you don’t know who did it? By using the transaction log, you can track which user dropped or deleted your database objects. And now, I’m going to explain to you how track user that dropped or deleted TSQL on SQL Server Objects.

hostforlife

First, let’s create a small Test Environment using the Transaction Log and the undocumented function “fn_dblog”.  This following below is the test environment to track a user who deleted TSQL.

1. Create a database and a table as below

 2. Now insert some data into the Test table:

 insert some data

 4. Now  find the deleted rows information using the Transaction Log and the function “fn_dblog”. Run the following command to get the info about all the deleted transactions.

 deleted transactions

In the above screen we can check that the last row says a Delete statement has been performed on a HEAP table “Test” in the “AllocUnitName” column under transaction ID- 0000:00000513.

5. Get the User Name that deleted the data from the “Test” table by using the Transaction SID and the preceding Transaction ID- 0000:00000513. Run the following command to get the information about the Transaction SID.

 Transaction

Now you can see the [Begin Time] of this transaction that will also help filter out the possibilities in determining the exact info like when the data was deleted by the user.

Determining  the user who droppped in TSQL

1. Now drop a table to the Test table

drop table Test

 2. To determine the dropped table information using the Transaction Log and the function “fn_dblog”. Run the following command to get the info about all the dropped transactions.

 fn_dblog

Now, you can see the [Begin Time] of this transaction that will also help filter out the possibilities in determining the exact info, like when the table was dropped by the user.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. They deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. They have customers from around the globe, spread across every continent. They serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

hostforlife banner