-- Code for SQL Server 2008 to LOG then prevent a DROP database through a server-wide trigger.  

-- Chris Skorlinski

-- Microsoft SQL Server Escalation Services

-- http://blogs.msdn.com/chrissk/

--adapted from

-- http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/5b1b81c9-ff0f-49a0-8a8c-cd217954bfcb/

/****** Object:  DdlTrigger [ddl_trig_database]    Script Date: 01/11/2010 19:05:57 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [ddl_trig_Prevent_Drop_Database]

ON ALL SERVER

FOR DROP_DATABASE

AS

--log attempt to drop database

DECLARE @db VARCHAR(209)

SET @db = (SELECT 'Database Dropped Attempted by ' +  CONVERT(nvarchar(100), ORIGINAL_LOGIN()) +

' executing command: '+ EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(229)'))

RAISERROR(@db, 16, 1)WITH LOG

--prevent drop database

ROLLBACK

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

--turn on trigger

ENABLE TRIGGER [ddl_trig_Prevent_Drop_Database] ON ALL SERVER

--test trigger

CREATE DATABASE test1

DROP DATABASE test1

Msg 50000, Level 16, State 1, Procedure ddl_trig_Prevent_Drop_Database, Line 11

Database Dropped Attempted by TestSQLUser executing command: DROP DATABASE test1

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

--turn off trigger

DISABLE TRIGGER [ddl_trig_Prevent_Drop_Database] ON ALL SERVER

GO

/****** Object:  DdlTrigger [ddl_trig_Prevent_Drop_Database]    Script Date: 01/11/2010 19:22:28 ******/

IF  EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'ddl_trig_Prevent_Drop_Database')

DROP TRIGGER [ddl_trig_Prevent_Drop_Database] ON ALL SERVER

GO

--cleanup current errorlog

sp_cycle_errorlog