DB query log

Hello,
We are using SQL Server 2000 DB for our project.Some 10 ppl are using the server.Today we found that a key table records are missing.It has lot of referntial integrity constraints.Could anyone tell me how to track who deleted the table..Does the server keeps any log of all the queries executed by the clients.Pls help us.ThanksProbably one of the ten most requested things for SQL. Unfortunately, no; natively SQL does not have this capability. There are 3rd party tools for doing this sort of thing, but nothing native to SQL Server 7.0 or 2000 (maybe in 2005, but I haven't seen that yet).

Some things that you CAN do to minimize/mitigate the problem:
1. Establish a proper security model. Each user has an assigned username / password (I prefer integrated security). Each user should be assigned the minimum privileges necessary to complete there assigned task(s). No one (not even you) should be using the sa account.

2. Establish a backup and recovery process that meets your specific requirements. Suggested profile would be a complete backup daily for system and user databases, plus transaction log backups every 1-3 hours depending on your requirements and the traffic on your server. Be sure that your backups are written to another server so that you can recover in the event of a failure of the disk on the primary server.

3. Audit the access to your SQL server; at a minimum log failed access requests.

4. Periodically audit the privileges for each user; check in particular for sysadmin or dbowner privileges.

I'm sorry about your loss and I hope that you are able to recover. I wish I had a better answer (and I hope MS comes up with a better one soon!). Hopefully it's not one that will cost anyone their job and you can chalk it up to experience.

Kindest regards,

hmscott

PS. I should mention that one thing many experts recommend is to run a continuous trace file of the most recent commands issued to SQL server. This IS a native capability of SQL server. I think there is an article on how to set one up on http://www.sqlservercentral.com. This would tell you who had done what and when (provided it happened within the window of the log file.

Hello,
We are using SQL Server 2000 DB for our project.Some 10 ppl are using the server.Today we found that a key table records are missing.It has lot of referntial integrity constraints.Could anyone tell me how to track who deleted the table..Does the server keeps any log of all the queries executed by the clients.Pls help us.Thanks|||Hello,
We are using SQL Server 2000 DB for our project.Some 10 ppl are using the server.Today we found that a key table records are missing.It has lot of referntial integrity constraints.Could anyone tell me how to track who deleted the table..Does the server keeps any log of all the queries executed by the clients.Pls help us.ThanksRestore from yesterday's backups, and follow Scott's suggestions...You DO have backups, right?|||Thanks for your reply...We do have backups