SQL Monitoring and Security 2

[Bullnuts]

Recently one of my SQL 2005 SP2 databases had something strange happen to it. For some reason and entire table inside the DB was completly emptied. no data left at all. There are only a few people with "sa" equivalent access(using windows Auth) to this DB and no one said they were even in that DB. I was able to restore the table and all was great.

I would really like to know the steps I should take in the future to be able to track who deleted it (username, workstation name/IP, time stamp, etc.) Our current recovery model for that DB is "Simple"

Thanks

 

[mutley1]

You should try using a SQL profiler trace.

 

[Mich]

Was the table dropped or all of the data deleted from the table?

If you want to audit DDL events (DROP, CREATE, etc)then I suggest setting up DDL triggers. They are a nice addition to SQL 2005 and perfect for auditing.

Auditing DML (INSERT, DELETE, etc) is a different monster. Some options,

- You can use Profiler. Audit SQL:StmtStarting and SQL:StmtCompleted. Be careful because the trace files can grow and get away from you. I'd also suggest running the trace from a different machine.

- You can use DML triggers, but I would only do that on tables that aren't changed often (what is your definition of often?) as the overhead might be too much.

- You can use an OUTPUT statement in your objects. This would have a similar performance hit as triggers plus you'd have to change all of your objects plus this wouldn't audit ad hoc statements. Not the best solution in my opinion.

- An interesting concept is to use a CLR trigger. I've never done this, but you can find some info on it here ==>

http://www.sqljunkies.ddj.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

- If you've got the budget you might want to look into some 3rd party tools.

Hopefully one of these options suits your needs.

-If it ain't broke, break it and make it better.