how to find out who dropped a database

Question

Post reply

how to find out who dropped a database

Hughesie

Grasshopper

Points: 21
More actions
March 30, 2010 at 2:07 am

#139967

database droppped by script. has since been restored, any way I can find out which user done it?
sql server 2005

Viewing 8 posts - 1 through 8 (of 8 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Try this SELECT [Transaction Id], [Begin Time], [UID], [SPID]

FROM ::fn_dblog (NULL, NULL)

WHERE [Transaction Name] = 'DROPOBJ'GO

above code can give you spid of user who dropped that

and then use below code to check other details

declare @error table ( logdate datetime,processinfo varchar(50), error_text nvarchar(max))

insert into @error

exec xp_readerrorlog

select * from @error where processinfo = 'spid126'

this is untest solution which came to my mind while seeing your this problem so better test it.

Still i am not sure how much it can help you.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 2

THe following gives a good guide to pulling the relevant info from the default trace

http://blogs.msdn.com/cindygross/archive/2009/12/16/sql-server-s-default-trace.aspx

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Andrew Gothard-467944 (3/30/2010)
THe following gives a good guide to pulling the relevant info from the default trace
http://blogs.msdn.com/cindygross/archive/2009/12/16/sql-server-s-default-trace.aspx

But for this Default trace should be enabled Default trace - A Beginner's Guide[/url]

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 4

Bhuvnesh (3/30/2010)
Andrew Gothard-467944 (3/30/2010)
THe following gives a good guide to pulling the relevant info from the default trace
http://blogs.msdn.com/cindygross/archive/2009/12/16/sql-server-s-default-trace.aspx
But for this Default trace should be enabled Default trace - A Beginner's Guide[/url]

It's enabled by default

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

Andrew Gothard-467944 (3/30/2010)
It's enabled by default

you are right, thanks for correcting me

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 6

Bhuvnesh (3/30/2010)
Andrew Gothard-467944 (3/30/2010)
It's enabled by default
you are right, thanks for correcting me

np - we're all here to learn, glad to have helped

Hughesie Grasshopper Points: 21 More actions · Answer 7

hey all

thanks. gonna try the trace now. wasnt even aware of it.