Finding out who dropped a table using the transaction log

I came across a question on ServerFault this afternoon that inflamed my desire to be ultra-geeky (it was really already inflamed after teaching backup and restore internals all afternoon). Basically the question boiled down to how to find out who dropped a table if there’s no other way except the transaction log (e.g. no tracing is available, even the default trace). So I hacked around and figured out at least how to find out *when* a particular table was dropped plus who dropped it.

Everything hinges on using undocumented commands to look into the transaction log. I’ve played with this before on the blog: fn_dblog.

First off I created a script to create a database, populate a table and then drop it.

USE [master];
GO
CREATE DATABASE [FnDbLogTest];
GO
USE [FnDbLogTest];
GO

CREATE TABLE [TestTable] (
[c1] INT IDENTITY,
[c2] CHAR (100) DEFAULT ‘a’);
GO
CREATE CLUSTERED INDEX [TT_CL] ON [TestTable] ([c1]);
GO
INSERT INTO [TestTable] DEFAULT VALUES;
GO

SELECT OBJECT_ID (N’TestTable’);
GO

DROP TABLE [TestTable];
GO

First we need to find the transactions that drop tables in the log:

SELECT [Transaction Id], [Begin Time], SUSER_SNAME ([Transaction SID]) AS [User]
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = N’DROPOBJ’;
GO

The (NULL, NULL) is the starting LSN and ending LSN to process – NULL means process everything available. 

Results are:

Transaction Id Begin Time              User
————– ———————– —————
0000:000000e0  2009/06/16 18:23:03:320 APPLECROSS\Paul

Now, this only shows us that a table was dropped, not which table it was. There’s no way to get the name of the table that was dropped, only the object ID – so you’ll need to have some other way to determine what the table ID is if there are multiple table drops and only one of them is malignant.

This code will give you the object ID of the dropped table:

SELECT TOP (1) [Lock Information] FROM fn_dblog (NULL, NULL)
WHERE [Transaction Id] = ‘0000:000000e0’
AND [Lock Information] LIKE ‘%SCH_M OBJECT%’;
GO

Lock Information
————————————————
HoBt 0:ACQUIRE_LOCK_SCH_M OBJECT: 25:245575913:0

The 25:245575913 is the database ID and object ID of the table that was dropped, and you can look up the object ID in an earlier backup of the database.

Now you can go find whoever it was and take whatever action you deem appropriate :-)

Hope this helps!

PS If you find the you don’t get enough info from fn_dblog, try turning on trace flag 2537. It allows the function to look at *all* possible log, not just the active log.

35 thoughts on “Finding out who dropped a table using the transaction log

  1. Hi Paul
    I think we are able to get such info in SQL Server 2005 onwards by using Database Triggers, what do you think?

  2. Awesome Dude! So happy to see you discuss this too!!! Back in phracking cold January, I dug into this topic also:
    http://www.sqlservercentral.com/blogs/hugo/archive/2009/01/17/it-s-minus-twenty-five-outside-and-i-m-writing-about-transaction-log-files.aspx
    I’ve added an alert script for those of you using Database mail, furthermore, I did a dumbed-down demo just like this last week in at SQLteach in Vancouver. Great topic for the extra geeky DBAs :)

  3. ::fn_dblog.AllocUnitName column is usuall the object touched upon, but I’ll dig into this over the coming week, interesting note about the role issue, thanks a whole bunch.

  4. It seems this doesn’t work in SQL Server 2008. I tried 10.0.2531 and 10.0.1600, both same error.

    Msg 207, Level 16, State 1, Line 1
    Invalid column name ‘UID’.

  5. Paul
    Thanks for great article.
    As someone mentioned above in 2008 no UID field ..

    However i am able to co-relate with Transaction SID with my NT account even tho i am in my global DBA group(dropped the table).
    I am quite sure that Transaction SID is tied with my Windows security ID . This information not aviable in SQL SERVER( may be in Active Directory)

    "Microsoft Windows security ID that is associated with the original_login_name. Is not nullable" (sys.dm_exec_session)

    SELECT [Transaction Id], [Begin Time], [SPID] ,[TRansaction SID]
    FROM ::fn_dblog (NULL, NULL)
    WHERE [Transaction Name] = ‘DROPOBJ’

  6. I just wanted to point out to a fact that an object deletion can happen as part of a transaction. In this case the investigation becomes a tad more tedious. In short, the DROPOBJ transaction will not be there. There will be ‘user_transaction’ instead. Also, in my case the context was LCX_NULL, and operation LOP_BEGIN_XACT. In the lock information field I got HoBt 0:ACQUIRE_LOCK_SCH_M METADATA: database_id = 5 SEQUENCE($seq_type = 0, object_id = 523148909). That object ID was the one deleted.

  7. Forgot to added that once you have that long number either from the [Description] or [TRansaction SID] column e.g. user_transaction;0x010500000000000515000000b0e022bfe57461a9e3436aa95d0e0000 you can use function SUSER_SNAME to return the actual user identity (or domain account) as

    SELECT SUSER_SNAME(0x010500000000000515000000B0E022BFE57461A9E3436AA95D0E0000);

    Result: DomainName\FirstName.LastName

  8. Hi Paul,

    Thanks for such good article.

    Can I use this for finding who deleted Sql Job also?

    If yes please help me how?

    Regards
    Sarma

  9. Hallo Sarma,

    all sql jobs are maintained in msdb. In msdb you find e.g. dbo.sysjobs which holds information about the job itself, dbo.sysjobsteps for each single step and so on.

    If you want to investigate changes to the jobs you have to search in the fn_dblog() for LOP_MODIFY_COLUMNS Operation and the corresponding AllocUnitName (e.g. %sysjobs%).
    To get information about the user you have to use SUSER_SNAME in conjunction with the attribut [Transaction SID] to retrieve the login of the person who did the changes.

  10. Hi,

    Question 1 :
    I have SQL Server 2000 (8.0.2040), and I cant find the column [Transaction SID] in ::fn_dblog.

    Question 2 :
    Can we do the same thing with DBCC LOG ?

  11. Dear Sir ,

    Sorry for being ambiguous
    my SQL Server version is 2008 , My transaction file reached 16GB tried to take a backup and restore for test but couldn’t , So please I need your help to minimize the Log File with no transactions of back and restore I hope it’s possible

    thank you
    Ruba

  12. Hi Paul,

    What will happen in case the user had executed Checkpoint after dropping table. Can we still get that information using fn_dblog() about dropping table.

    Thanks so much for sharing this excellent article. :)

    1. Unless they’re in the SIMPLE recovery model, nothing will happen as a checkpoint only tries to clear the log in the SIMPLE recovery model. If they’re in SIMPLE, you might still be able to get the info from the log using trace flag 2536/2537 (version dependent). And you’re welcome!

  13. Great Article.
    I was searching mssql 2005 ,it was not showing fn_dblog for delete.
    Dbcc traceon(2537,-1) give proper delete details.

  14. Hi Paul,
    In my company all users are login with “sa”
    And some uses server for work and some have SQL management studio on their system and they connect to sever,

    In this seconario how can I find who drop table or who deleted table/database

    Can I know windows login..?? From where the query triggered..??

    Please help ASAP

    1. It’s a lot harder as there’s no named transaction – it’s just called ‘user transaction’. You need to look for an LOP_MODIFY_ROW on the AllocUnitName sys.sysschobjs.clst, but there’s no guarantee that’s a name change.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.