MS SQL Server - Determine When/Who Deleted Database

Author

Determine When/Who Deleted Database

Matt

2006-12-04, 7:12 pm

I have SQL Server 2000 running and a database that I use is now missing. It
appears it has been deleted along with it's log files. Is there a way to
determine when, how and who deleted this database?

Tibor Karaszi

2006-12-04, 7:12 pm

SQL Server does not log these things by default. So, unless you have some auditing practices in
place (using Profiler or the C2 trace, for example), you can most probably not see anything.

You might be able to get some information about the "when" part by looking at the backup history
tables in the msdb. but that will only tell you when the last backup was performed for the database,
and deleting a db using EM has an option to also wipe out those entries.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Matt" <mamarsha@newsgroups.nospam> wrote in message news:%23CJ7gI8FHHA.3268@TK2MSFTNGP04.phx.gbl...
>I have SQL Server 2000 running and a database that I use is now missing. It appears it has been
>deleted along with it's log files. Is there a way to determine when, how and who deleted this
>database?
>

David Portas

2006-12-04, 7:12 pm

Matt wrote:
> I have SQL Server 2000 running and a database that I use is now missing. It
> appears it has been deleted along with it's log files. Is there a way to
> determine when, how and who deleted this database?

Who has the sysadmin or dbcreator roles on your system? That should be
a fairly small group in most cases. sysadmin, dbcreator and the
database owner are the only people who can execute the DROP DATABASE
statement. Who else has access to the server?

When was it last backed up? That information will help narrow down the
time when it was deleted.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Charles Wang[MSFT]

2006-12-07, 5:17 am

Agree with David.
Actually it is hard to track the historical information if you did not
setup audit mechanism in your enterprise network. You may consider to setup
audit policy in your enterprise netword from now on to secure your
information.
I would like to provide you an link for your reference:
http://www.microsoft.com/technet/se...rity/tcg/tcgch0
3n.mspx

I recommend that you may have a new post at Windows security newsgroup to
consult if there is a best practice to setup an audit system.

Please feel free to let me know if you have any other questions or concerns.

Sincerely,
Charles Wang
Microsoft Online Community Support

Sponsored Links