A table in the production database has 'mysteriously' disappeared.
Does anybody know of any way to diagnose what the heck happened to it? And who did it?
Edit 1: This is an internal app, with weak security. All of the apps (except mine of course 😉 is vulnerable to SQL Injection, but our users are very unsophisticated and the table name was not one that might be immediately obvious, so I don't think it was an SQL Injection (not that it matters … kind of beyond the scope of the question).
Edit 2: Also, just an FYI; this table has been around for a long time, so it wasn't 'undone' with a restore.
Best Answer
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.