Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jun 15 '12 at 15:09

This question came from our site for professional and enthusiast programmers.

2  
The new way to do this is with Policies. –  Nick Chammas Jun 15 '12 at 15:25
4  
And the old way to do this is with code review! –  HLGEM Jun 15 '12 at 17:03
add comment

1 Answer 1

up vote 11 down vote accepted
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.

enter image description here

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:

enter image description here

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

share|improve this answer
2  
+1 Great answer and clever approach. Not to mention the awesome error message :) –  Thomas Stringer Jun 15 '12 at 14:04
    
You're obviously familiar with Policy-Based Management. Can this type of restriction be better enforced using PBM? –  Nick Chammas Jun 15 '12 at 16:38
1  
I like the return message, too. I might even hard-code the name of the only guy here that uses cursors. Tempting. Nah. –  William Meitzen Jun 15 '12 at 16:54
    
Sadly, the Connect item I raised has been closed as "won't fix." –  Aaron Bertrand Nov 21 '12 at 0:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.