Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Won't Fix Help for as Won't Fix


4
0
Sign in
to vote
Type: Suggestion
ID: 749317
Opened: 6/15/2012 10:24:19 AM
Access Restriction: Public
0
Workaround(s)
view

Description

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/
Details
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
Sign in to post a workaround.