who deleted a SQL table?

Question

Hi,

In the production server i see that there were 2 important table which got suddenly missing. This was an absolute surprise for us. due to this our application went down. Finally we could create the tables back using the old backups and the applicaiton came to normal state after that.

now the question is who deleted the tables, how can i find who deleted them?

is there a way i can peep into the transaction logs to find the same or something like that?

[UPDATE]

This is windows based application. there is no internet connection configured in this server. the only parties having this privilege is the client and our company. At this point both are blaming each other.

[UPDATE END]

Thanks

Nikhil Sreenivas

Answer 1

How many users have table delete privileges? Maybe you can simply ask them all.

Answer 2

More importantly that knowing who have deleted the tables, you should limit the access of your users.

I suppose this user has the right to drop table which is kind of bad. I your application user is sa or have full rights on the DB you should change the that as soon as possible.

for knowing what happened, if it is a web application you might find some hint in the webserver log files. For a desktop application, if you have some logs this might helps. If your user have access to the DB server directly, I don't think you can do much about it.

Answer 3

Yes. The transaction log almost certainly has this information in it, and there are tools that let you examine the log. For years Lumigent sold a great product called Log Explorer that did this. I'm not sure they still sell it; they're not actively promoting it so you would have to call them. ApexSQL has a similar tool, which I haven't used; it costs $999 though.

There are also products that run continuously and monitor your databases, to ensure that there's an audit trail whenever something like this happens. Of course it's too late for that now.

(Full disclosure: I used to work for Lumigent.)

Answer 4

Since you didn't set up the tools to monitor the database correctly, you may have difficulty getting back this information. The best thing to do is decide how to prevent the problem from reoccurring.

You say at this point both client and your company are blaming each other. Truth is, it is your company's fault no matter what individual dropped the tables because you didn't take action to protect your database that you should have as part of designing a database. First, no user should have drop table rights. Exactly two individuals at most should have the rights to drop a table on prod, the dba and his or her backup person. If you had correctly set up your database using stored procs for data access, no one who wasn't an admin would be able to directly access the tables in any way shape or form. That's why stored procs are the best way to go for database security.

Second, even when it isn't a web app, you should make sure your design protects against injection attacks as even company users get disgruntled and try such tactics. Third, you should set up DDL triggers to track who makes changes to structure. And while you are fixing the problems you created, you should also see if the client wants to pay for adding auditing to important tables to be able to track who made data changes.

Answer 5

By default SQL Server keeps a system trace which tracks, amongst other things:

includes events for monitoring server starts and stops, object creation and deletion, log and data file auto growth, and other database changes.

The system trace is recycled every so often, but under most circumstances it keeps data for several days. Open the trace and look for Object:Deleted events. The record will contain the time when this occurred, the login of the user doing the change, the application name and client host name from where the command was issued.