禁止包含游標的新 UDF

有沒有辦法阻止開發人員創建使用游標的新使用者定義函式？可以讀取 UDF 程式碼的數據庫觸發器可以嗎？

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

需要注意的是，這也將禁止包含評論的觸發器，例如：

/* 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 */

…或者如果您有非常不便的查詢，例如：

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

…或者即使你呼叫你的函式：

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

編輯

在這裡解決尼克的問題而不是作為評論，因為它會變得有點囉嗦。

PBM 對於某些東西非常有用，但在其他東西上卻不是那麼好。此要求屬於“其他東西”類別。主要問題是定義（例如OBJECT_DEFINITION()）沒有作為屬性暴露給使用者定義的函式等方面。這意味著您的條件不能簡單地表達如下：

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

如果是這種情況，您可以創建這個條件，圍繞它包裝一個策略，將其設置為“On prevent: change”，然後去吃午飯。要將其作為一項政策實施，需要做更多的工作。

由於@ObjectDefinition 不是此方面的有效欄位，因此我們需要使用ExecuteSql(). 所以你的情況必須是這樣的：

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

醜陋，對吧？當 this 評估為 0 時，策略應該成功；當它是 <> 0 時，策略應該失敗。（請記住，策略應該以您希望系統處於的狀態而不是您不處於的狀態來表達。）

因此，首先，您可以通過打開對象資源管理器、展開管理 > 策略管理、右鍵點擊條件並選擇新條件… 為其命名，選擇使用者定義的函式方面，然後點擊高級編輯按鈕. 在那裡您可以輸入ExecuteSql()上面的字元串，然後點擊“確定”。

將運算符更改為 =，輸入 0 作為值，然後點擊確定。現在創建一個策略。右鍵點擊 Policies，New Policy… 為其命名，選擇剛剛創建的條件，然後選擇評估模式：

哦哦。為什麼“防止”操作不可用？On prevent: 改變當然可以讓你阻止函式被創建。因為像 ID 和定義這樣的屬性不能通過 facet 獲得，需要我們通過ExecuteSql()，所以我們會受到限制，以防止帶有條件的策略ExecuteSql()自動執行。雖然2008 年的這篇 PBM 部落格文章表明此限制已被取消，但我仍然發現它在 SQL Server 2012 中實施，並應用了累積更新 #1 (11.0.2316)。

所以現在，您可以使用策略來實現這一點，但您將無法阻止創建此類功能 - 您只能在事後調查違規行為（通過選擇 On Demand 或 On Schedule）。請記住，即使您按需執行此策略，它也使用與 DDL 觸發器完全相同的邏輯，因此需要注意相同的警告：如果您的評論或有效的非游標查詢包含相同的單詞序列。

如果您希望 facets 可用的屬性更靈活、更完整，這將允許在使用有利於 DDL 觸發器的策略時進行更多控制，請對以下兩個 Connect 項目進行投票和評論：

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

我不知道有什麼項目可以解決總體上放寬限制的問題ExecuteSql()。如果找不到，我稍後會查找並歸檔。我認為部落格文章反映了您現在可以按計劃執行這些的事實，但是如果您可以按計劃執行它，為什麼它不能在“預防”模式下工作？

結束循環：這是新的 Connect 項目。請投票和/或添加描述您的案例/業務需求的評論（這通常比原始投票更有價值）：

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

引用自：https://dba.stackexchange.com/questions/19325