RE: [SQLDownUnder] Master Database & Oops

[Wheat, Mitch]

Title: RE: [SQLDownUnder] Master Database & Oops

Very neat Greg.

Mitch

-----Original Message-----
From: Greg Low [mailto:greglow@xxxxxxxxxxxxx]
Sent: Wednesday, 2 March 2005 3:02 PM
To: sqldownunder@xxxxxxxxxxxxxxxxxxxxxx
Subject: [SQLDownUnder] Master Database & Oops

Hi Folks,
 
Just realised something I can do with SQL Server 2005.
 
Ever accidentally run a script in the master database instead of some other database it was meant to run in? Most people working with SQL Server have done it more than once. Just built myself a script with SQL Server 2005 to set a DDL trigger that stops DDL actions in the master database, unless they refer to the trigger itself. Very sweet!

 
Regards,
 
Greg
 
PS: ALTER CONTRACT is not yet supported although it's in the list of DDL events. There apparently will also soon be a DDL_ALL option. That will make it simpler.

 
/* ProtectMasterDDL Trigger Example
   - March 2005 by Greg Low
   - greg.low@xxxxxxxxxxx <mailto:greg.low@xxxxxxxxxxx>
*/
CREATE TRIGGER ProtectMasterDDL
ON DATABASE FOR
  CREATE_TABLE,ALTER_TABLE,DROP_TABLE,
  CREATE_VIEW,ALTER_VIEW,DROP_VIEW,
  CREATE_SYNONYM,DROP_SYNONYM,CREATE_FUNCTION,
  ALTER_FUNCTION,DROP_FUNCTION,
  CREATE_PROCEDURE,ALTER_PROCEDURE,DROP_PROCEDURE,
  CREATE_TRIGGER,ALTER_TRIGGER,DROP_TRIGGER,
  CREATE_EVENT_NOTIFICATION,DROP_EVENT_NOTIFICATION,
  CREATE_INDEX,ALTER_INDEX,DROP_INDEX,
  CREATE_STATISTICS,UPDATE_STATISTICS,DROP_STATISTICS,
  CREATE_ASSEMBLY,ALTER_ASSEMBLY,DROP_ASSEMBLY,
  CREATE_TYPE,DROP_TYPE,
  CREATE_USER,ALTER_USER,DROP_USER,
  CREATE_ROLE,ALTER_ROLE,DROP_ROLE,
  CREATE_APPLICATION_ROLE,ALTER_APPLICATION_ROLE,DROP_APPLICATION_ROLE,
  CREATE_SCHEMA,ALTER_SCHEMA,DROP_SCHEMA,
  CREATE_MESSAGE_TYPE,ALTER_MESSAGE_TYPE,DROP_MESSAGE_TYPE,
  CREATE_CONTRACT, --ALTER_CONTRACT
  DROP_CONTRACT,
  CREATE_QUEUE,ALTER_QUEUE,DROP_QUEUE,
  CREATE_SERVICE,ALTER_SERVICE,DROP_SERVICE,
  CREATE_ROUTE,ALTER_ROUTE,DROP_ROUTE,
  CREATE_REMOTE_SERVICE_BINDING,ALTER_REMOTE_SERVICE_BINDING,DROP_REMOTE_SERVICE_BINDING,
  GRANT_DATABASE,DENY_DATABASE,REVOKE_DATABASE,
  CREATE_XML_SCHEMA_COLLECTION,ALTER_XML_SCHEMA_COLLECTION,DROP_XML_SCHEMA_COLLECTION,
  CREATE_PARTITION_FUNCTION,ALTER_PARTITION_FUNCTION,DROP_PARTITION_FUNCTION,
  CREATE_PARTITION_SCHEME,ALTER_PARTITION_SCHEME,DROP_PARTITION_SCHEME
AS
  IF CHARINDEX('ProtectMasterDDL',CONVERT(varchar(max),EVENTDATA())) = 0
  BEGIN
     PRINT 'You may not perform DDL operations on the master database without disabling the ProtectMasterDDL trigger.'
     ROLLBACK
  END
GO

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net

EOM

---

EMAIL DISCLAIMER NOTICE

This message is the property of HATCH.  The information in this email is confidential and may be legally privileged. It is intended solely for the addressee.  Access to this email by anyone else is unauthorised.  If you are not the intended recipient, any disclosure, copying,distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful.

If you have received this message in error please notify HATCH immediately via e-mail to : mailadmin@xxxxxxxxxxxx

For more details about HATCH visit http://www.hatch.com.au

---

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net