Post reply

How to tell who deleted a database from SQL Server 2012?

  • September 15, 2014 at 11:01 am

    #397345

    Hello,

    Last Thursday somebody deleted a database from one of our SQL Server 2012 servers. The MDF and LDF files were removed. We had a backup and restored OK, but now trying to find out who deleted the database. The server has been restarted since then and now it’s Monday (9/15).

    1. Can you tell who deleted a database or data from a Trace Data File (*.TRC)?

    2. How to have more trace files (*.TRC)? There are only 4 TRC files.

    3. When the server is rebooted, does it recycle these trace data files?

    4. Can you tell who deleted a database or data from a transaction files (*.TRN)?

    5. Can you tell who deleted a database or data from a transaction log file (*.LDF)?

    6. If you did not have TRN, LDF or TRC log files, then how can you tell who deleted a database?

    7. Why the SQL Server Log Files did not show when the database was dropped? It shows when the database was restored and backed up and other things, but not when it was dropped or detached.

    8. Any T-SQL that could be executed to show who did ?

    Thank you for all of you help.

  • September 15, 2014 at 11:06 am

    #1745542

    1) If you mean the default trace, yes, providing the data is still in the trace. It's 5 files of 20MB each, no more.

    2) You don't.

    3) When SQL restarts or when the file reaches 20MB

    4) No, because master can't have log backups taken

    5) Highly unlikely, because master is in simple recovery, which means the log is marked reusable on checkpoint.

    6) You don't. Unless you have some custom auditing (which is a very good idea)

    7) Because it's not an error (I'll argue that the backup messages shouldn't go there either). It's the error log, not an audit log.

    8) No.

    Should be a short list, it'll be people who had db_owner rights on that database or sysadmin.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 16, 2014 at 12:37 am

    This was removed by the editor as SPAM

  • September 16, 2014 at 10:51 am

    #1745836

    Quick thought, if no auditing is in place, start with the short list Gail mentioned.

    😎

Viewing 4 posts - 1 through 3 (of 3 total)

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