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];
GOCREATE 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;
GOSELECT OBJECT_ID (N’TestTable’);
GODROP 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%’;
GOLock 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.
37 thoughts on “Finding out who dropped a table using the transaction log”
Hi Paul
I think we are able to get such info in SQL Server 2005 onwards by using Database Triggers, what do you think?
Very nice. Paul this is very complex subject explained in easy manner.
Kind Regards,
Pinal
Nice article Paul
As per the comment that’s true if you setup a DDL Trigger for auditing pro-actively. What Paul’s showing is how to get the info after the fact. If you want an example of a ddl trigger – check out this post: http://www.sqlskills.com/blogs/kimberly/post/EXECUTE-AS-and-an-important-update-your-DDL-Triggers-(for-auditing-or-prevention).aspx
Cheers,
kt
And of course the option to use the default trace which, if not already overwritten or restarted captures the relevant data.
-Jens
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 :)
::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.
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’.
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’
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.
Good point!
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
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
Hi Sarma,
You can Track it Down, too by Investigation the Log of Msdb.
Jobs are located in dbo.sysjobs!
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.
How to find out who has deleted the database?
Looking in the default trace:
DECLARE @path varchar(256)
SELECT @path = path
FROM sys.traces
where id = 1
SELECT *
FROM fn_trace_gettable(@path, 1)
Hi Paul,
How could we know the name of the object that was deleted?
You’d have to look for the row being deleted from sys.sysschemaobjs and convert the name to ASCII.
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 ?
1) I don’t remember anything back in 2000 – lots of new logging stuff in 2005
2) I don’t know – I never use it
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
Look at Kimberly’s post that explains – google for ‘Kimberly 8 steps’
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. :)
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!
Great Article.
I was searching mssql 2005 ,it was not showing fn_dblog for delete.
Dbcc traceon(2537,-1) give proper delete details.
That’s because your log had cleared and the log records were no longer in the active portion of the log. Same in all versions.
Hi Paul,
How can I get program name who did transaction?
thank you
No, not once the transaction has finished.
Hi Paul,
How do I find out who RENAMED a table? Tx.
You’d need to look for a specific row changing in sys.sysschobjs – that’s the topic of a whole blog post.
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
You need to use the Transaction SID. See https://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/
How do I find out who RENAMED a table?
Database is in full recovery model
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.
Hi Paul,
How could we recover mysql table that was deleted/droped by a hacker?
No idea – we only work with SQL Server. But I imagine some kind of backup would be your best approach.