Its commonly happened at all work place where a user trigger a DROP 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.

As a standard, to find such users is with the help of the default trace, because the default trace captures and tracks database activity performed on SQL Server instance, but if your system is very busy in that case the trace files may roll over far too fast and it may not be able to catch some of the changes in database.  But these changes are also tracked in the transaction log file of the database and we will use this to find the users in questions.

To achieve this, we can use undocumented function fn_dblog
to find any unauthorized or unapproved deletes or table drops. The below tip will help to track or find any unethical or an unwanted user who has dropped a table or deleted data from a table. Its strongly suggested, Please don’t test this tips on production environment, test first it with test/Dev lab environment.

Here, I have created a Database Named ‘VIRENDRATEST’ and a create a Table Named ‘tblTest’ as

–Creating DB VirendraTest

USE [master]
GO

CREATE DATABASE VirendraTest;
GO

— Creating Table tblTest

USE VirendraTest;
GO

CREATE TABLE tblTest ID INT IDENTITYOrd_Date DATETIME DEFAULT GETDATE ())

— Inserting dummy test Data

INSERT INTO tblTest DEFAULT VALUES
GO 100

Now, suppose, you have deleted record having ID=10 as

Delete tblTest where ID =10

Now here, find the user who deleted row/rows , steps are as below

  1. Find transaction ID from :SELECT [Transaction ID],Operation, Context,AllocUnitName FROM fn_dblog(NULL, NULL)
    WHERE Operation ‘LOP_DELETE_ROWS’


  2. Now We have found the transaction ID from the above command which we will use in the below command to get the transaction SID of the user who has deleted the data from below command,
    SELECT Operation,[Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
    FROM fn_dblog(NULL, NULL) WHERE [Transaction ID]‘0000:00000752’
    AND [Operation] ‘LOP_BEGIN_XACT’


  3. Using Transaction ID , we can get exact user name as

    Select SUSER_SNAME(0x01050000000000051500000060A4EEB0789A03300FF709CFE8030000)

    Above syntax will show exact user name.


    Below is the list from where we can find out operations timing with these types of details (It’s not a complete list, I just get it from self-practice and some Google)

Sr. No. OPERATION DESCRIPTION
1 LOP_ABORT_XACT  Indicates that a transaction was aborted and rolled back.
2 LOP_BEGIN_CKPT  A checkpoint has begun.
3 LOP_BEGIN_XACT  Indicates the start of a transaction.
4 LOP_BUF_WRITE Writing to Buffer.
5 LOP_COMMIT_XACT Indicates that a transaction has committed.
6 LOP_COUNT_DELTA  
7 LOP_CREATE_ALLOCCHAIN New Allocation chain
8 LOP_CREATE_INDEX Creating an index.
9 LOP_DELETE_ROWS Rows were deleted from a table.
10 LOP_DELETE_SPLIT  A page split has occurred. Rows have moved physically.
11 LOP_DELTA_SYSIND   SYSINDEXES table has been modified.
12 LOP_DROP_INDEX Dropping an index.
13 LOP_END_CKPT Checkpoint has finished.
14 LOP_EXPUNGE_ROWS Row physically expunged from a page, now free for new rows.
15 LOP_FILE_HDR_MODIF   SQL Server has grown a database file.
16 LOP_FORGET_XACT Shows that a 2-phase commit transaction was rolled back.
17 LOP_FORMAT_PAGE   Write a header of a newly allocated database page.
18 LOP_HOBT_DDL  
19 LOP_HOBT_DELTA  
20 LOP_IDENT_NEWVAL Identity’s New reseed values
21 LOP_INSERT_ROWS   Insert a row into a user or system table.
22 LOP_LOCK_XACT  
23 LOP_MARK_DDL Data Definition Language change – table schema was modified.
24 LOP_MARK_SAVEPOINT Designate that an application has issued a ‘SAVE TRANSACTION’ command.
25 LOP_MIGRATE_LOCKS  
26 LOP_MODIFY_COLUMNS   Designates that a row was modified as the result of an Update command.
27 LOP_MODIFY_HEADER   A new data page created and has initialized the header of that page.
28 LOP_MODIFY_ROW   Row modification as a result of an Update command.
29 LOP_PREP_XACT Transaction is in a 2-phase commit protocol.
30 LOP_SET_BITS  
31 LOP_SET_BITS Designates that the DBMS modified space allocation bits as the result of allocating a new extent.
32 LOP_SET_FREE_SPACE   Designates that a previously allocated extent has been returned to the free pool.
33 LOP_SORT_BEGIN  A sort begins with index creation. – SORT_END end of the sorting while creating an index.
34 LOP_SORT_EXTENT Sorting extents as part of building an index.
35 LOP_UNDO_DELETE_SPLIT The page split process has been dumped.
36 LOP_XACT_CKPT During the Checkpoint, open transactions were detected.

Please  let me know about description’s missing values in above table.  🙂  

Advertisements
Comments
  1. Ganesh says:

    Great and informative !

    Thanks Virendra.

    Like

  2. mandaroke says:

    Awesome…

    Like

  3. zcla says:

    Nice article, very userful. Thanks a lot!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s