Preventing Accidental Schema Changes To The ‘master’ Database

June 8th, 2008 Adrian Banks 111373 Views

If you have ever run some SQL within SQL Server Management Studio only to realise that you've run it against the master database by mistake, you'll know that it can sometimes be hard to undo the damage.

A simple way to stop these accidental changes is to create a database trigger that will prevent any schema changes to the master database:

USE master
GO

CREATE TRIGGER StopSchemaChanges ON DATABASE FOR
CREATE_APPLICATION_ROLE, ALTER_APPLICATION_ROLE, DROP_APPLICATION_ROLE,
CREATE_ASSEMBLY, ALTER_ASSEMBLY, DROP_ASSEMBLY,
CREATE_CERTIFICATE, ALTER_CERTIFICATE, DROP_CERTIFICATE,
GRANT_DATABASE, DENY_DATABASE, REVOKE_DATABASE,
CREATE_EVENT_NOTIFICATION, DROP_EVENT_NOTIFICATION,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_INDEX, ALTER_INDEX, DROP_INDEX,
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_SCHEMA, ALTER_SCHEMA, DROP_SCHEMA,
CREATE_STATISTICS, DROP_STATISTICS, UPDATE_STATISTICS,
CREATE_SYNONYM, DROP_SYNONYM,
CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW
AS
BEGIN
    RAISERROR(N'Do you really modify the master database?', 16, 1) WITH NOWAIT
    ROLLBACK TRANSACTION
END
GO

Any time you attempt to change the master database, SQL Server will fail with an error. If you do want to make a schema change, simply disable the trigger and then re-enable it once the schema change is complete.

(NB. this only works with SQL Server 2005)

Entry Filed Under: SQL Server 2005, SQL Server


Subscribe to this feed Bookmark this post on del.icio.us Digg this post on digg.com Share this post

Leave a Comment

Required

Required, hidden

Please enter the following word: magic word

Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>

Subscribe to the comments via RSS Feed


Most Popular Posts



Calendar

August 2014
M T W T F S S
« Oct    
 123
45678910
11121314151617
18192021222324
25262728293031

Most Recent Posts

Related Posts

RSS Feeds Feeds

Visitors

Visitor Map

Total Hits: 769449
Hits Today: 543