8

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.

4
  • You mean the "Not Me" ghost got you too? Jun 16, 2009 at 21:40
  • do you have separate database accounts for everyone or does everybody log in as 'dba' or something equivalent?
    – Zerofiz
    Jun 16, 2009 at 21:43
  • @Zerofiz-We're using Windows Integrated Security, so yes every user can be identified. Jun 16, 2009 at 21:46
  • I came across this blog which explains the step by step process to determine who dropped the table dbarepublic.com/2015/01/who-dropped-table.html.
    – user264790
    Jan 13, 2015 at 21:05

7 Answers 7

14

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.

1
  • Thanks Paul, that's great advice. I can wait. I'm leaving now, and going to another client tomorrow, so I'll be trying to figure out what happened on Thursday. I will tell the admin about the log backup. Jun 16, 2009 at 23:37
8

Maybe it was Little Bobby Tables...

2
  • 1
    Nice :) Would +1 it for humour but that would be silly as the only other answer has 1 vote as well.
    – John_
    Jun 16, 2009 at 22:08
  • 2
    Nah, that's funny enough for an upvote. Jun 16, 2009 at 23:43
2

You might be able to recover this information out of the SQL logs.

4
  • I know this information is in the SQLServer logs, but I thought you couldn't read anything from them. I'd love to find out you can. Anybody know? Jun 16, 2009 at 21:43
  • Im only familiar with Sybase SQL Anywhere, but they have a utility to translate log files into SQL statements...
    – Zerofiz
    Jun 16, 2009 at 21:44
  • 1
    This tool might be able to help you read the logs. red-gate.com/products/SQL_Log_Rescue/index.htm
    – RSolberg
    Jun 16, 2009 at 21:46
  • +1 for the log tool link. Why don't you put it into the answer? Jun 16, 2009 at 23:41
2

If the default trace log is running, all the info is stored in the log folder. You should be able to see when the object (table) was dropped and by which connection that did it. But this type of permission should be only given to DBAs anyway

2

I am trying to fix a corrupt MSDB. Sorry I am not able to elaborate.

Run these and it should give a general idea where to look, assuming your default trace is on.

SELECT * FROM ::fn_trace_getinfo(default)

SELECT t.EventID, t.ColumnID, e.name as Event_Description, c.name as Column_Description FROM ::fn_trace_geteventinfo(1) t JOIN sys.trace_events e ON t.eventID = e.trace_event_id JOIN sys.trace_columns c ON t.columnid = c.trace_column_id

1
  • Thanks, I think the sys.* tables are 2005 though aren't they? Is there a 2000 equivalent? Jun 16, 2009 at 23:38
2

Only way to find out this info is by reading transaction log (assuming it’s in full recovery mode).

Two ways to do this:

  • Third party tools such as ApexSQL Log or SQL Log Rescue (free but sql 2000 only)
  • Using commands such as DBCC LOG or fn_dblog – none of which is well documented unfortunately
2

In SSMS you can try right-clicking on the dB and navigating through Reports -> Standard Reports -> Schema Changes History.

Right click on the report and 'SaveAs' Excel and find your object namne.

You will not be able to get anything if the server has been restarted more than five times after the object had been dropped.

1
  • Looking at the logs failed for me. Most were overrun due to the high number of transactions in our system. Your method worked perfectly. Thanks for sharing! Feb 9, 2016 at 16:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .