Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need a trigger to stop a restore with replace Expand / Collapse
Author
Message
Posted Monday, June 9, 2014 8:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:09 PM
Points: 70, Visits: 320
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
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER TRIGGER [ddl_StopDoingThat]
ON ALL SERVER
FOR CREATE_DATABASE, DROP_DATABASE
AS

IF SUSER_SNAME() = 'domain\user'
BEGIN

PRINT 'You must disable Trigger "StopDoingThat" to create databases!'
ROLLBACK

END
GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO




Post #1578833
Posted Monday, June 9, 2014 8:19 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:18 PM
Points: 3,301, Visits: 7,125
I'm not sure if you can do that, but why don't you remove the permissions to restore backups on the database for that user?


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1578838
Posted Monday, June 9, 2014 8:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:40 AM
Points: 42,419, Visits: 35,482
Not possible, because DDL triggers are AFTER triggers. They fire after the action has completed. Any trigger which fired after a restore database had run would not be able to undo the restore, you can't roll back a restore.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1578839
Posted Monday, June 9, 2014 8:32 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:42 AM
Points: 5,226, Visits: 9,428
Don't bother with the trigger. Grant the user CREATE ANY DATABASE, but make sure he/she/it is not (a) the owner of any database you don't want overwritten, (b) a member of sysadmin and (c) a member of dbcreator.

John
Post #1578850
Posted Monday, June 9, 2014 8:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:09 PM
Points: 70, Visits: 320
Thanks for the quick answers.

If DDL triggers run after, why does the trigger I have that prevents someone from dropping a database work?
Post #1578853
Posted Monday, June 9, 2014 8:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:42 AM
Points: 5,226, Visits: 9,428
Because you can roll back a DROP DATABASE, but not a RESTORE.

John
Post #1578857
Posted Monday, June 9, 2014 8:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:09 PM
Points: 70, Visits: 320
Changing permissions is the best way to handle this I agree, but a surprisingly slow battle. A large chunk of people run with SysAdmin even though only DBA's should have this privilege.
Post #1578859
Posted Monday, June 9, 2014 8:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:09 PM
Points: 70, Visits: 320
If it's not possible then I guess I'll stop trying to find a way and let the requester know. Thanks, this was quick :)
Post #1578860
Posted Monday, June 9, 2014 9:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:40 AM
Points: 42,419, Visits: 35,482
lmacdonald (6/9/2014)
If DDL triggers run after, why does the trigger I have that prevents someone from dropping a database work?


Because you can run a drop database within a transaction, the 'results' (the files being deleted) does not happen unless the transaction is committed, if you roll back the transaction the files are never dropped.




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1578876
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse