[SQLDownUnder] Master Database & Oops

[Greg Low]

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