I have a SQL Server 2005 database that has been deleted, and I need to discover who deleted it. Is there a way of obtaining this user name?
-
Did you checked the sql logs?– CaterpillarJun 29, 2009 at 11:07
-
2@Evan, This is NOT an ass-kicking exercise! In my enlightened company, we will offer the offending user an intense period of 're-education'. If that doesn't work, it time for the baseball bats...– TangiestJun 29, 2009 at 15:13
-
+1 for using a cousin of a clue-by-four, still the most effective re-education process on the planet today.– Avery PayneJun 29, 2009 at 17:45
-
@MagicAndi - Sorry-- just couldn't resist adding the tag after remembering it on the "Who dropped a table" question a couple of weeks back. smile– Evan AndersonJun 29, 2009 at 18:31
-
1Is ass kicking really necessary? While it was a bad mistake, it was a mistake. Restore the backup. If one doesn't have a backup, perhaps the ass kicking should be refocused in to a backup and recovery model...– Frank VJul 7, 2009 at 16:03
2 Answers
If it's not there in the default trace still, see my answer to kind-of this question on ServerFault a couple of weeks ago at Is there any way to determine who dropped a table?. It links to a blog post I wrote for 2000, 2005, 2008 to find out when a table was dropped and who did it - you could adapt it for a database too. Checkout my blog post at Finding out who dropped a table using the transaction log.
Hope this helps!
PS Checkout DDL Triggers as a way to prevent this happening in future.