I have recently created a policy that checks stored procedure names and prevents any to be created and/or changed to anything that is not prefixed with 'spr_'.
I have a simple Cindition called SPNameCondition who's expression is @Name LIKE 'spr_%'
I also have my Policy called SPNamePolicy who's condition is set to SPNameCondition. It is Enabled and targets every stored procedure in every database. I have the evaluation mode set to on change: prevent and no server restrictions.
After creating my policy, I checked it by evaluating it against all existing stored procedures in our databases. This worked as I expected as it flagged up all existing stored procedures names that didn't start with 'spr_' as in error.
The problem I am having though, is when I create a new stored procedure in SSMS and execute I get the following exception:
A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_execute_policy":
System.TypeInitializationException: The type initializer for 'Microsoft.SqlServer.Management.Dmf.PolicyEvaluationHelper' threw an exception. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.Diagnostics.STrace,
Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file spe
View Complete Post