How can I find who/what process dropped view/table from my sql server 2008 database ?

Question

How can I find who/what process dropped view/table from my sql server 2008 database ?

Joy Smith San

SSC-Insane

Points: 24877
More actions
January 23, 2013 at 12:31 am

#393996

Experts,
How can I find who/what process dropped view/table from my sql server 2008 database ?
Auditing is not implemented.
Thanks,
Smith

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

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

e4d4 SSCertifiable Points: 5777 More actions · Answer 1

If the DB is in full recovery mode, then you could try use software that analyze transaction log eg. http://www.red-gate.com/products/dba/sql-log-rescue/

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

You can check the default trace. It logs DDL along with the login. If enough time has passed that the event is not in the default trace, there's likely no way of telling unless you had some custom auditing in place.

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 3

..Thanks Gila... No it happened yesterday only. Can you please guide me how to go about it now.

Since it's very urgent, googling will take time.

Thanks again.

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

Default Trace - A Beginner's Guide - http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/

If the event is no longer in the trace then and you dont have auditing like DDL triggers, extended events setup then your out of luck finding who dropped it.

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 5

There is a built in report in SSMS that uses the default trace to show schema change history.

Right click on the database where the table was dropped from, then Reports -> Standard Reports -> Schema Changes History

Eric M Russell SSC Guru Points: 125562 More actions · Answer 6

This is why service, application, and user accounts should not be members of the SYSADMIN or DBO role. That would help prevent mysteriously dropped objects and also narrow the list of suspects ...

The default trace would be your best bet. However, if it's not running or for whatever reason doesn't appear to return what looks like a DROP statement, then there are a couple of other stabs you can take at it.

The last statement sent by a connection to SQL Server hangs around in the input buffer. So, if the connection that executed the DROP is still active, and the last command it executed was the DROP, then the following may reveal it. The following will dump the last command executed for each active connection, and SYSPROCESSES will also identify each connection by username, hostname, domain name, etc.

select * into #sp from sys.sysprocesses;

declare @spid int = 0;

while @spid >= 0

begin;

select @spid = min(spid) from #sp where spid > @spid;

if @@rowcount >= 1

begin;

select * from #sp where spid = @spid;

dbcc inputbuffer( @spid );

end else break;

end;

This is even less hopeful. I'm not sure if a DROP statement would be included here, just like default trace it gets cycled out over time, and I'm not even sure how to join back to a specific connection. However, because it's looking at this from another angle, it might still reveal something useful. What it does is query cached statment plans looking for any reference to a DROP.

SELECT deqs.last_execution_time, dest.TEXT AS [Query], *

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

WHERE dest.TEXT like '%DROP%'

ORDER BY deqs.last_execution_time DESC;

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho