Someone dropped a table...not good

Question

Someone dropped a table...not good

mgraulich

Ten Centuries

Points: 1294
More actions
July 27, 2006 at 7:50 am

#60675

I am supproting a server that houses a third party vendor app. Someone apparently dropped a table in the db and I want to see who did this. Can I read the transaction log in order to see this? Any good tools out there to do this?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

Hi,

Your best bet will be something like Lumigent's Log Explorer or SQL Log Rescue by Red Gate. There are similar products out there on the market.

All of these products depend on you either having log backups or having an untouched log. If you're running in simple recovery mode or the log was truncated you've got no hope of finding out who did it short of someone owning up to it.

Hope that helps. Good luck,

Karl
source control for SQL Server

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 2

If auditing is enabled on the server then, you can find the id from there as well.

------------
Prakash Sawant

http://psawant.blogspot.com

mgraulich Ten Centuries Points: 1294 More actions · Answer 3

Forgive my ignorance but are you talking about SQL auditing? How do I turn that on? Where is the information stored?

SQLZ SSChampion Points: 12872 More actions · Answer 4

I believe Prakash is talking about C2 auditing, which can be enabled using sp_configure. I'd check it out in BOL first and I'd advise caution before using it because it audits absolutely everything and can quickly fill up your drives and adversely affect performance.

And of course, it won't do anything to help you figure out who dropped the table (unless you already had it turned on).

Moving into the future, rather than enabling auditing, I would suggest that you limit permissions so that only a select few people can run DDL statements like DROP TABLE. And in third-party databases, no one but the DBA(s) should have that kind of level of permissions. At least, that way, you can be certain that it must have been one of a few people. Preventing it from being able to happen in the first place is a better strategy than having to play detective once the crime has happened.

Karl
source control for SQL Server

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 5

Yes i was refering to C2 auditing only.

thanks Karl for explaning.

------------
Prakash Sawant

http://psawant.blogspot.com