Results 1 to 4 of 4

Thread: DB query log

  1. #1
    Join Date
    Jul 2004
    Posts
    2

    Unanswered: 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.Thanks

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Probably 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.

    Quote Originally Posted by vsri78
    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
    Have you hugged your backup today?

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by vsri78
    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
    Restore from yesterday's backups, and follow Scott's suggestions...You DO have backups, right?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jul 2004
    Posts
    2

    DB Query

    Thanks for your reply....We do have backups

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •