If you need to prevent changes to your SQL Server Schema, or at least monitor them, then this post is worth reading

I needed to monitor/control changes made to SQL server databases at server instance level. There are plenty of ways of doing this but there is nothing as simple as a DDL level trigger. It’s clean, can be disabled quickly for rapid change implementation and its a central location which means deploying to sever SQL Servers or a server farm is that much easier.

So the basic requirements
1. We must be able to monitor CREATE,DROP and ALTER on Tables, Views, Stored Procedures, Triggers and Functions
2. We must be able to filter based on user, applying the rule or not
3. We must have a log of each schema change regardless of success or failure
4. We need to send an email when a change has been attempted and was disallowed

So how are we going to do this. Well we can use the ROLLBACK inside our trigger to prevent the changes from occurring, and we can use the RAISERROR to display the appropriate message we want to user/program to receive when the attempt fails. We can also take advantage of the code SQL Server query execution sequence such that after a rollback has been called in a trigger, the parent transaction has been dumped, but any further transactions will continue to be executed and committed. See here http://msdn.microsoft.com/en-us/library/ms187844(v=sql.105).aspx

Some Key fundamentals we are going to be using
First off the

, this will allow us to create the trigger at server level, so it affects all databases. It will hence be located in the Server Objects >> Triggers within SQL Server Management Studio
Next comes

which contains all the details we want about what is changing and who is trying to change it
We will be using “

” to identify the current logged in connection that is behind the event and also filter on this to make a decision to reject or approve the change.
Finally we use the

and

commands to initiate a rejection of the event, followed with our logging insert and in my case an email send method, I won’t get into the details of the mail sending in this article, however its worth mentioning that I have a fully fledged SSIS package driven mail sending engine integrated with MS Exchange, its possible to send from any mailbox to any mail group or address in the active directory or individual mail address book, you can attach files from the network or from binary within the database, you can send with importance in text format or html as this example demonstrates, Its a very powerful and very handy engine to have in the systems topology, took 3 days to write and about a month to fine tune, suddenly sending mail is very easy from anywhere in your infrastructure. Anyway I digress

Enough background, here’s the Code. You will obviously need to adjust bits to suite your needs before you use this
This should be executed against the master