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

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 749317 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 6/15/2012 10:24:19 AM
Access Restriction Public


In this blog post:

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:
Sign in to post a comment.
Posted by Microsoft on 11/26/2012 at 9:47 AM
This issue is moved into a planning cycle of next major release. This request will be tracked by the PBM feature team.

Best regards