Database drops

Question

Post reply

Database drops

imSQrLy

Ten Centuries

Points: 1335
More actions
November 8, 2010 at 6:43 am

#143336

I recently had a client where someone dropped 2 databases thinking they were on the test sql server. I was able to see a connection to the production sql server, in the default trace file from a persons laptop just before the databases went missing. This person admitted to dropping the database. Don't ask why security was so relaxed at this site, that's an issue for the client to deal with.
The problem is, other than the default trace showing a connection and a few other tid bits of info, i could not find the actual drop occurring in the sql error log.
My question is, is a drop\delete of a database logged anywhere by default? If you know of a way, other than third party transaction log tools, to see a drop of the db please chime in!
Jimmy
"I'm still learning the things i thought i knew!"

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

imSQrLy (11/8/2010)
My question is, is a drop\delete of a database logged anywhere by default?

In the default trace (that's what it's there for)

The highlighted two rows (straight from the default trace) show the start and end of the drop.

The object type tells you that this was a database being dropped, the databasename gives the name of the DB being dropped, the login name and domain say who and the who name says from where.

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

imSQrLy Ten Centuries Points: 1335 More actions · Answer 2

Gail,

Thanks, I was looking at the same lines in the default trace. I saw the object:deleted, but did not put together with the object type to prove that was when the deleted happened. I knew that's when it happened, but again couldn't prove it until now. Thanks for pointing it out.

Jimmy

"I'm still learning the things i thought i knew!"

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 3

imSQrLy (11/8/2010)
I recently had a client where someone dropped 2 databases thinking they were on the test sql server. I was able to see a connection to the production sql server, in the default trace file from a persons laptop just before the databases went missing. This person admitted to dropping the database. Don't ask why security was so relaxed at this site, that's an issue for the client to deal with.
The problem is, other than the default trace showing a connection and a few other tid bits of info, i could not find the actual drop occurring in the sql error log.
My question is, is a drop\delete of a database logged anywhere by default? If you know of a way, other than third party transaction log tools, to see a drop of the db please chime in!

Another one method -->Check the Schema change histroy.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

muthukkumaran (11/9/2010)
Another one method -->Check the Schema change histroy.

That report queries the default trace

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 5

GilaMonster (11/9/2010)
muthukkumaran (11/9/2010)
Another one method -->Check the Schema change histroy.
That report queries the default trace

Gail,Thanks for the info.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/