One of the things that Policy Based management is really good at is making sure that people don’t create stored procedures that start with “sp_”. However when the server that you are deploying the policies to is also being used for SQL Server replication this can get a little more complex seeing how all of the replication stored procedures start with “sp_” by default and they aren’t marked as system stored procedures.
I came across this problem when setting this up on a clients system. The way that I was able to get around this was to setup Policy to evaluate against a condition which I had setup which filtered out the stored procedures that were used for SQL Server Replication. I did this by setting up a condition, which I called “User Defined Stored Procedures” which had two values in the Expression. The first was looking at the Schema field and excluding anything in the “sys” schema (which takes care of all the system objects), then looking at the Name field and excluding everything that matched “sp_MS%”. You can see this condition below (click any of the images to enlarge).
Now to ensure that this only ran against the user databases I created another condition against the Database facet which looked at the IsSystemObject field and make sure that it was False, shown below. That way I could put procedures like sp_whoisactive and sp_who3 into the master database and not have a problem with them.
The actual Check condition of the policy was setup easily enough, simply checking that the stored procedure name wasn’t like “sp%” as shown below.
Bringing this all together is the actual Policy which is configured with the check condition, and is configured to filter the objects being checked against the two other check conditions which helps to limit the amount of time that the policy takes to execute as shown below. As this is a SQL Server 2008 R2 instance in this example I had to use a schedule to verify everything nightly, but that’ll do.
Hopefully if you run across this situation this will help you get everything setup faster that I was able to.
Denny
