SQL SERVER – Who Dropped Table? Part 2

I have received a lot of email as a follow-up email of Who Dropped Your Table? – SQL in Sixty Seconds #112. Many wanted to know if there is any workaround available where at least we can get some information. Of course, there is a way to find out few more things. However, before we continue I strongly suggest that you watch the following video about the dropped table.

Once you have watched the video, let us continue our journey with a small experiment.

Now let us create a table in the database and right after that we drop it. Here is the script for the same.

CREATE TABLE ImpTable (ID INT)
GO
DROP TABLE ImpTable
GO

Now you can run the following command which will list the beginning time and name of the user who dropped the recent table.

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

Here is how the result will look.

There you go, in this short blog post we can figure out who dropped your table. Well, there are no object id or table names in the results but all of them can be figured out by doing additional searches. However, that is the topic for other blog posts.

Here are my few recent videos and I would like to know what is your feedback about them. You can subscribe to my youtube channel here.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.