Policy Health State for Policy Based Management demo

Hi,

A few days ago, I created a policy which used to validate the names of procedures. No procedure must start with "sp_".

Today while working, I found something interesting that reflect the procedures which don't meet the policy condition. The information is available from Database to object level.

Following is the policy:

Now let's look at Object Explorer, how we can validate if there is any object that doesn't match this policy or any other policy. Make sure that the policy is enabled.

In the above image, when we click on the server, it display all the objects in Object Explorer. Now let's click on the Show Policy Health State for all nodes button, which is highlighted.

This changes the Databases object to Critical under Policy Health State column. Let's drill down to the actual objects, which are causing the database to be critical. First will be which type of database(s) is/are critical.

This shows that LearningCT database health state is critical. Let's drill down again to find which object(s) is/are in critical state.

This shows that Stored Procedures have some issues. Let's drill down again to find which stored procedures are not adhering to the policy.

Ok, now we see that 2 procedures doesn't comply with the policy, which says that there shouldn't be any procedure that starts with "sp_", but we have 2 procedures.

We can select both procedures and choose Policies=>Delete Health States, it prompts to delete policy health state of the selected objects. Click on OK button will remove the health state of policy.

Now refresh the screen.

If we run through the process again, it will again show the health state of policy.

This is very helpful when we have not enforced any policy but want to know which objects fail the policy check. Another way to enforce this from happening is to prevent users from creating the objects which does not satisfy policy condition.

Hope this will be helpful.

Happy SQL Coding.