DDL trigger to prevent drop of a particular database

Question

DDL trigger to prevent drop of a particular database

S_Kumar_S

SSCrazy Eights

Points: 9037
More actions
June 27, 2013 at 4:55 am

#274495

Can I write a DDL trigger to prevent drop of a particular database? I tried this but didn't work:
CREATE TRIGGER Prevent_DB_Drop
ON ALL SERVER
FOR DROP_DATABASE
AS
DECLARE @oname NVARCHAR(100)
SELECT @oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')
IF @oname IN ('testDB')
BEGIN
PRINT 'You are not allowed to drop this Database.'
ROLLBACK;
END
GO
ENABLE TRIGGER [Prevent_DB_Drop] ON ALL SERVER
drop database test
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

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

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

you had most of it, it looks like, but you forgot to handle the EVENTDATA() function, which ahs the currently scoped event information

CREATE TRIGGER Prevent_DB_Drop

ON ALL SERVER

FOR DROP_DATABASE

AS

DECLARE @oname NVARCHAR(100),

@eventData XML

--set the EVENTDATA() functions value to our xml so we can parse it

SET @eventData = EVENTDATA()

SELECT @oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')

IF @oname IN ('testDB')

BEGIN

PRINT 'You are not allowed to drop this Database.'

ROLLBACK;

END

GO

ENABLE TRIGGER [Prevent_DB_Drop] ON ALL SERVER

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 2

Interestingly I got this script from one of your script for tables and modified it for database. While pasting here, I missed the part that you added. But after all this , it doesn't work.

I can happily drop the database with sa account without any error or warning.

Lowell (6/27/2013)
you had most of it, it looks like, but you forgot to handle the EVENTDATA() function, which ahs the currently scoped event information
CREATE TRIGGER Prevent_DB_Drop
ON ALL SERVER
FOR DROP_DATABASE
AS
DECLARE @oname NVARCHAR(100),
@eventData XML
--set the EVENTDATA() functions value to our xml so we can parse it
SET @eventData = EVENTDATA()
SELECT @oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')
IF @oname IN ('testDB')
BEGIN
PRINT 'You are not allowed to drop this Database.'
ROLLBACK;
END
GO
ENABLE TRIGGER [Prevent_DB_Drop] ON ALL SERVER

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Lowell SSC Guru Points: 323546 More actions · Answer 3

ahh, i see it now;

objectname is fine for objects, like tables/procs(anything that exists in sys.objects), but you need a different field in the xml data for the database name