Email Alert from On Change Log Only
Published: Jul 07, 2014
Having Policy Management set up is great… being able to schedule a job to start up, check your systems, and report anything that doesn’t fit your ideal is fantastic. But what about those policy breaches which cannot be automatically rolled back (On Change: Prevent is not an option) but for which you want to be immediately notified?

On Change: Log Only will happily notify the Windows Event Log that someone has breached policy but who, in all honesty, ever has that open all day on constant refresh?

Never fear… there is a solution. Get the SQL Server Agent to react to an Event Notification and send you an immediate email with all the details so that you can quickly and effectively fix the problem.

To do this we first need to know how Policy Management interacts with the Event Log:



So, using this table we can see that On Change: Log Only will send an Event of number 34053 to the Windows Event Log. Let’s capture this using SQL Server Agent.

Expand SQL Server Agent and then right Click Alerts. Choose “New Alert…”



The one Policy I’m using as an example is, in this case, a very basic “All Databases must be in SIMPLE mode”:



So… in our New Alert Window we will provide a meaningful name and associate the Alert with an Error Number of 34053…



We then switch to the “Response” tab and tick the option to notify an Operator (in this case me)…



Lastly, on the “Options” tab we will tick the box to include the alert error in the Email and add a little bit of extra text to ensure that we know which server this came from…



Press OK and we’re done. We now have our custom alert in order to pick up a breach of policy. This will work with ALL policies (new and old) set to On Change:Log only without any changes being needed to the Alert.

Let’s test it…

alter database myDatabase set recovery full

Instantly I received the following in my Inbox (edited to protect privacy of course)…



Which is exactly what we want. Simple. You can now quickly and easily stay on top of any policy breaches as they happen and put in the appropriate fixes.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron