Re: [SQLDownUnder] Master Database & Oops

[Darius Zakrzewski]

Hi Greg,

Very nice indeed! Thanks for sharing this with us :-)

Cheers,
Darius.

----- Original Message -----
From: "Greg Low" <greglow@xxxxxxxxxxxxx>
To: <sqldownunder@xxxxxxxxxxxxxxxxxxxxxx>
Sent: Wednesday, March 02, 2005 6:01 PM
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_SERVI
CE_BINDING,
  GRANT_DATABASE,DENY_DATABASE,REVOKE_DATABASE,

CREATE_XML_SCHEMA_COLLECTION,ALTER_XML_SCHEMA_COLLECTION,DROP_XML_SCHEMA_COL
LECTION,

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






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