SQL CLR — ошибка разрешения на TSQL UDF, даже если у пользователя есть доступ

То, что здесь происходит, не задокументировано нигде, что я мог найти, но, похоже, требуется указать SystemDataAccess = SystemDataAccessKind.Read в атрибуте SqlFunction (по умолчанию он установлен на None) при выполнении запросов, содержащих T-SQL Scalar UDF или Multi-statement TVF, НО только если функция не привязана к схеме. Это означает, что вам может сойти с рук SystemDataAccess = SystemDataAccessKind.None, если ссылочная функция:

• встроенный TVF
• скалярная UDF или TVF с несколькими операторами, созданная с использованием параметра WITH SCHEMABINDING

Имеет смысл, что у встроенных TVF нет проблем, поскольку их определение втягивается в запрос, в котором на них ссылаются, и они не вызываются как отдельные объекты.

Я предполагаю, что причина требования «системного» доступа к данным для не привязанных к схеме UDF и TVF с несколькими операторами заключается в том, что представления системного каталога, вероятно, необходимо проверять во время выполнения, чтобы убедиться, что все еще существуют какие-либо зависимости и разрешения. действительны, поскольку нельзя гарантировать, что они будут такими же, как они были изначально, из-за отсутствия привязки схемы (что предотвращает определенные изменения объектов, которые являются зависимостями других объектов).

Имея это в виду, самым быстрым/самым прямым исправлением (которое позволяет SystemDataAccess оставаться неопределенным или, по крайней мере, иметь значение None) будет добавление этой опции в UDF следующим образом:

ALTER FUNCTION [be].[udfDivide](@Numerator FLOAT, @Denominator FLOAT,
                                @ValueIfDivideByZero FLOAT)
RETURNS FLOAT
WITH SCHEMABINDING -- this option helps in a few ways
AS
BEGIN
    DECLARE @Result FLOAT;
    IF (@Denominator = 0)
    BEGIN
      SET @Result = @ValueIfDivideByZero;
    END;
    ELSE
    BEGIN
      SET @Result = (@Numerator / @Denominator);
    END;

    RETURN @Result;
END;

НО, если этот запрос выполняется так часто, вам, безусловно, следует преобразовать его во встроенный TVF. Существует огромный удар по производительности при использовании UDF, и ITVF обычно являются ответом на этот вопрос. Вам просто нужно выразить ту же логику в виде одного запроса (используйте CASE для «если-то-иначе»), а затем использовать его через CROSS APPLY, а не в списке SELECT, оба из которых достаточно просты.

Здесь та же логика, что и у Inline TVF:

CREATE FUNCTION [be].[itvfDivide](@Numerator FLOAT, @Denominator FLOAT,
                                @ValueIfDivideByZero FLOAT)
RETURNS TABLE
WITH SCHEMABINDING -- not necessary, but still a good idea to use
AS RETURN
   SELECT CASE @Denominator
             WHEN 0 THEN @ValueIfDivideByZero
             ELSE (@Numerator / @Denominator)
          END AS [Result];
GO

А вот и простой тест:

SELECT itvf.[Result]
FROM   (VALUES (1, 2, NULL), (3, 0, -5.789), (1.1, 5.67567, -999)
       ) tbl(num, den, dbz)
CROSS APPLY be.itvfDivide(tbl.[num], tbl.[den], tbl.[dbz]) itvf;

Этот ITVF будет намного быстрее, чем текущий Scalar UDF :-).