Allow On Change: Prevent for policies with ExecuteSql() conditions by AaronBertrand

In this blog post:

http://blogs.msdn.com/b/sqlpbm/archive/2008/07/03/executesql.aspx

The PBM team (I assume Dan Jones at the time) said:

"Because of this the initial implementation prevented policies using ExecuteSql() from being automated. [...] We subsequently relaxed this restriction. We recognized how powerful this capability is and the early user feedback indicated a strong desire to automate policies containing SQL."

While it is true that a policy containing ExecuteSql() can now be *scheduled*, it can't truly be automated. In SQL Server 2012 I am still prevented from using the evaluation mode of "On Change: Prevent" or even "On Change: Log" if any of the expressions in the associated condition uses ExecuteSql().

If an administrator is setting up such a policy, and signs on the dotted line about the risk of running scripts, why can't such a policy be automated? It would be really useful to be able to, say, prevent a function containing a cursor from being created, instead of finding it after the fact (and after maybe it has been executed multiple times, and has brought the server down in the meantime).

This gap in functionality means that DDL triggers are still the preferred way to prevent such a function from being created in the first place. I wrote quite a novel about this in response to this dba.stackexchange question:

http://dba.stackexchange.com/questions/19325/disallow-new-udfs-that-contain-a-cursor/