I have created a trigger to stop someone from dropping or creating a new database that works fine. However, recently I restored over a database that was replicated. I have now been asked if I can create a trigger that will block restoring over an existing database. I thought my trigger would stop these things but after testing I can still restore over existing databases without conflicting with the trigger. I know that restore_database clause is not an option when creating the trigger, so I am wondering if any other users have a found a way to do this? As you can see this one is for a specific user, I will figure out how to specify a specific next.
Thanks for any help. This is what I have so far.
/****** Object: DdlTrigger [ddl_StopDoingThat] Script Date: 06/09/2014 09:38:21 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER TRIGGER [ddl_StopDoingThat]
ON ALL SERVER
FOR CREATE_DATABASE, DROP_DATABASE
IF SUSER_SNAME() = 'domain\user'
PRINT 'You must disable Trigger "StopDoingThat" to create databases!'
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF