Disallow new UDFs that contain a cursor

Question

Is there a way to prevent developers from creating new user-defined functions that use a cursor? Would a database trigger that can read the code of a UDF do?

Answer 1

CREATE TRIGGER PreventCursorUDFs
    ON DATABASE 
    FOR CREATE_FUNCTION
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EventData XML = EVENTDATA();

    IF LOWER(@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]','NVARCHAR(MAX)'))
      LIKE N'%declare%cursor%fetch%'
    BEGIN
        RAISERROR('Yo, no cursors in functions!', 11, 1);
        ROLLBACK;
    END
END
GO

With a caveat, this will also disallow a trigger that contains a comment, like:

/* we used to do this a dumb way, using DECLARE CURSOR and FETCH */
/* now we're a little smarter and use this table-valued function */

...or if you have really inconvenient queries such as:

SELECT [declare] = [cursor] * 10 FROM dbo.[fetch];

...or even if you call your function:

CREATE FUNCTION dbo.ModeClarevoyantForCursoryFetching()
RETURNS TABLE
AS ...

EDIT

Addressing Nick's question here instead of as a comment, because it's going to get a little long-winded.

PBM is great for some stuff, but not so great at other stuff. This requirement falls under the "other stuff" category. The main problem is that the definition (e.g. OBJECT_DEFINITION()) is not exposed as a property to facets like user-defined functions. This means your condition cannot simply express something like:

@ObjectDefinition NOT LIKE '%DECLARE%CURSOR%FETCH%'

If this were the case, you could create this condition, wrap a policy around it, set it to "On prevent: change" and go to lunch. To implement this as a policy requires a little more work.

Since @ObjectDefinition is not a valid Field for this facet, we need to obtain it using ExecuteSql(). So your condition would have to be something like:

ExecuteSql('Numeric', 'SELECT x = PATINDEX(''%declare%cursor%fetch'',
  LOWER(OBJECT_DEFINITION(OBJECT_ID(@@SchemaName + ''.'' + @@ObjectName))))')

Ugly, right? When this evaluates to 0, the policy should succeed; when it is <> 0, the policy should fail. (Remember that a policy is supposed to be expressed in terms of the state you want the system to be in, not the state that you don't.)

So to start you would create a condition by opening Object Explorer, expanding Management > Policy Management, right-clicking Conditions, and selecting New Condition... Give it a name, choose the User Defined Function facet, and click on the Advanced Edit button. There you can enter the ExecuteSql() string above and click OK.

Change the Operator to =, enter 0 as the Value, and click OK. Now create a Policy. Right-click Policies, New Policy... Give it a name, choose the condition you just created, and then choose the Evaluation Mode:

Uh oh. Why aren't "On prevent" actions available? On prevent: change of course would allow you to prevent the function from being created. Because properties like ID and definition aren't available through the facet, requiring us to go through ExecuteSql(), we are subject to a restriction that prevents policies with conditions using ExecuteSql() from being automated. While this PBM blog post from 2008 suggests that this restriction has been lifted, I'm still finding it is enforced in SQL Server 2012 with Cumulative Update #1 applied (11.0.2316).

So for now, you can implement this using a policy, but you won't be able to prevent such a function from being created - you will only be able to investigate violations after the fact (by choosing either On Demand or On Schedule). Keep in mind that even if you run this policy on demand, it uses the exact same logic as the DDL trigger, so is subject to the same caveat: it may introduce false positives if you have comments or valid non-cursor queries that include the same sequence of words.

If you want the properties available to facets to be more flexible and more complete, which would allow for more control in using policies in favor of DDL triggers, please vote for and comment on these two Connect items:

http://connect.microsoft.com/SQLServer/feedback/details/552345/pbm-add-objectid-as-a-parameter-for-executesql

http://connect.microsoft.com/SQLServer/feedback/details/649944/pbm-enable-the-ability-to-pass-more-parameters-to-executesql

I don't know of an item that addresses relaxing the restriction on ExecuteSql() in general. I will look later and file one if I can't find one. I think the blog post reflects the fact that you can now run these on a schedule, but if you can run it on a schedule, why can't it work in "on prevent" mode?

Closing the loop: here is the new Connect item. Please vote and/or add comments describing your use case / business need (this is often much more valuable than just raw votes):

http://connect.microsoft.com/SQLServer/feedback/details/749317/allow-on-change-prevent-for-policies-with-executesql-conditions