TechEd Europe 2008 Day Five – Policy Based Management

Bringing all the concepts I’ve described in the last few posts into place, you can see the results of what Alexi wants in a Policy. Navigating to the Management node in SQL Server Management Studio, he can create a new Policy, give it a name, and since he had the word "make sure" in his intent, he’ll set the execution mode to "Prevent on Change".

With that set, he’ll create a new condition – when he’s inside that designer, he’ll pick the "Stored Procedure" facet, and add a new condition. Remember his requirements are that the names of the Stored Procedures must start with "usp". So he now creates a condition using "@Name" , with the comparison as an "=" to the letters ‘usp%’. We always put a policy and a condition in the positive – we want to see where it does not conform, not where it does.

With all that in place, Alexi can "Evaluate" the policy, and it will show the violations. From here on out, Alexi’s Policy will prevent the action, since it is contained within a transaction.