Protect the master database in SQL Server 2005
Over the years, I've accidentally run scripts in the master database more times than I care to remember. I mentioned it in the Ascend class that Bob Beauchemin was running today and everyone went “oh yes, we do that all the time“. I then realised I can “fix“ 99% of that in SQL Server 2005 with a DDL trigger.
Enjoy!
USE master
GO
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