Sql-server – Determine Who Dropped a Database

You might be able to get the info out of the log using the undocumented ::fn_dblog function which interprets log records. I'm in the middle of teaching a disaster recovery class right now, but if you can wait 2-3 hours I'll post how to do it for you - should be able to get the username too without having to buy any tools (I used to spelunk around the log a ton in 2000 as I wrote a bunch of the internal log analysis code that DBCC CHECKDB uses in 2000).

[Edited to include instructions] Ok - finished teaching and I knocked up a blog post to show you how to analyze the log in 2000, 2005, 2008 to find out when the table was dropped and who did it. Checkout my blog post at Finding out who dropped a table using the transaction log. [/edit]

Do you still have the transaction log around? Which recovery model is the database in? If it's SIMPLE, don't do anything that would cause a checkpoint. If it's FULL or BULK_LOGGED, don't do a log backup. Either of these will cause the log to be truncated and then you may lose the ability to look back in the log, although I included a trace flag in the blog post that may help you with that too.

Thanks

PS One way of preventing table drops in 2000 without adding security is to create a simple schemabound view on it - DROP TABLE will fail if the view exists.

I seem to have stumbled upon the answer. It would appear that the original SQL server was an upgraded from 7 to 2000 to 2005! crazy but apparently has been in use for 8 years well before my time.

The problem seems to be related to the database compatibility, see the following msoft article

link text

"Databases set to compatibility level 70 or lower are not displayed"

I have tested this by changing the level to 90 and sure enough it now works.

thanks to all who replied, this site is going to be very useful.