Я использую контекстное соединение в скалярной функции SQL CLR. Функция создает динамический запрос, а затем выполняет его (получая данные/логику из разных мест для построения запроса).
Я могу "построить" динамическую строку вручную и запустить ее в стандартном окне Management Studio, и она работает нормально. Когда тот же запрос выполняется через CLR в контекстном соединении, я получаю сообщение об ошибке.
Я отладил эту вещь и обнаружил, что ошибка связана с проблемой разрешения на UDF TSQL, который вызывается в запросе. Созданное сообщение об ошибке:
Отказано в разрешении EXECUTE на be.udfDivide
Я знаю, что у меня есть доступ к этой определяемой пользователем функции, потому что она работает, когда я запускаю запрос вручную, но при запуске через SQLCLR происходит сбой, хотя код SQLCLR должен выполняться в моем контексте.
Запрос эффективно:
select be.udfDivide(sum(Numerator), sum(Denominator))
from Table where <some stuff>
В <some stuff> живет вся сложная логика, но я уверяю вас, что это просто предложение where, и эта функция CLR успешно запускается миллионы раз в день, когда в запросе нет ссылки на UDF.
С помощью некоторого творческого взлома запросов я смог подтвердить, что контекстное соединение выполняется под правильным пользователем (как SYSTEM_USER, так и CURRENT_USER), и я знаю, что у этого пользователя есть разрешения на UDF. Таким образом, похоже, что причиной этого является нарушение цепочки владения. Только пока не знаю, как это исправить.
Я знаю, что могу удалить UDF в этом конкретном случае, так как это простое деление (хотя UDF обрабатывает деление на ноль для нас), но в случае более сложных UDF мы хотели бы иметь возможность ссылаться на них в запросе.
Функция be.udfDivide приведена ниже для справки:
ALTER function [be].[udfDivide](@Numerator float, @Demoninator float,
@ValueIfDivideByZero float)
returns float
as
begin
declare @result float
if @Demoninator = 0
begin set @result = @ValueIfDivideByZero end
else
begin set @result = @Numerator / @Demoninator end
return @result
end
Ответ 1 · рейтинг 1 · Solomon Rutzky
То, что здесь происходит, не задокументировано нигде, что я мог найти, но, похоже, требуется указать 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 :-).
