Who Dropped A Table?

Last night, I  was paged for a missing table in production database. The ticket asked to recover the table structure along with its data ASAP. I was half asleep when I answered the page and assigned the ticket to myself. I picked up my phone and called the person who created the ticket. I verified the database name, server instance and the table name.

Now, I have minimum information to begin my research. I don't want to start the table recovery process right away just because the ticket asked me to do. Do not trust everything your requester says or put on the ticket. I would like to know if the table is really missing? Was the table ever moved to production database?  Ask which application and reports are being impacted. Verify that your requester is searching in a correct database and db Instance? 

Also. it does not hurt to ask for error message on the application or report which uses the missing table. The error message spit out from the application can sometime be misleading. For example “No Table Found”. or "ORA-00942: table or view does not exist". What does this mean?  This could be a permission issue meaning the user may not have an appropriate access to view the table or the table does not exist like it said.

By now, you might know the table was never moved to production or it was dropped accidentally by someone. If it was found or it never made it to the production db, you can close the ticket and get some rest. Unfortunately, the table was there for years but dropped by someone and we don't know who that someone is? It is very important to know who or what dropped it? Without finding who, I won't even try to recover the dropped table because it might get dropped again. Do you agree?

Let's find out who dropped a table? This is a multiple steps process for SQL Server which I consolidated into one query.  Are you ready to find the bad guy?

SQL Server

CREATE TABLE mytable 
  ( 
     cola INT PRIMARY KEY 
  ); 

INSERT INTO mytable 
VALUES      (1); 

DROP TABLE mytable; 

SELECT operation, 
       [transaction id], 
       Suser_sname([transaction sid]), 
       [transaction name], 
       [begin time], 
       [spid], 
       description 
FROM   Fn_dblog (NULL, NULL) 
WHERE  [transaction name] = 'DROPOBJ' 
go 

--This will display the db user who dropped the table.

Oracle DB

You will need to enable DDL trigger and you will find the detail on ddl event on the table where ddl trigger is designed to store. 

The above SQL will display the user account who dropped the table. The user could be someone who is a developer or it could be an application user. If this is an app user, you will need to do further research on which procedures or job dropped the table and then present that to the requester. If that is a developer, ask if that was an accident and then revise the role and privilege of all your production database users and make appropriate changes. Assigning correct roles and privileges will prevent such accident going forward. Stay tuned for next article where I will show you how to recover the dropped table.