Non parallel plan with row level security predicate to check server role
4
votes
0
answers
614
views
While exploring Row Level Security I picked up that whenever I had it enabled it was not creating any parallel query plans.
In SQL Server 2019 this gave me
NonParallelPlanReason="CouldNotGenerateValidParallelPlan"
In SQL Server 2022 this gave me the more detailed “NonParallelPlanReason="NonParallelizableIntrinsicFunction"
Where my security predicate looks like -
CREATE FUNCTION Security.fn_SecurityPredicateTenantId(@TenantId UNIQUEIDENTIFIER)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securityPredicateResult
WHERE
(
IS_SRVROLEMEMBER('RLS_Filtered_AppRole') = 1
AND CAST(SESSION_CONTEXT(N'TenantId') AS uniqueidentifier) = @TenantId
)
OR
(
IS_SRVROLEMEMBER('RLS_FullAccess_AppRole') = 1
OR IS_SRVROLEMEMBER('RLS_FullAccess_StaffUserRole') = 1
);
GO
The session context is fine, I don't have 11042 or 9432 flags enabled (as per the reply here https://dba.stackexchange.com/questions/308059/non-parallel-plan-only-in-azure-when-session-context-is-used)
This narrowed it down to the IS_SRVROLEMEMBER calls, sure enough running
select 1 as 'Hi' where IS_SRVROLEMEMBER('RLS_Filtered_AppRole') = 1
gives me the same intrinsic function error.
Thinking I'd have to tact to use database roles or principals I tried these, which both face the same error
select 1 as 'Hi' where IS_SRVROLEMEMBER('RLS_Filtered_AppRole') = 1
select 1 as 'Hi' where IS_MEMBER('RLS_Filtered_AppRole') = 1
select 1 as 'Hi' where DATABASE_PRINCIPAL_ID() = 1
Query plan of all 3 with NonParallelizableIntrinsicFunction - https://www.brentozar.com/pastetheplan/?id=SJPIYpVZT
Is there a better way how I should be checking the server/database roles of my users within my row level security predicate function?
This has been reported to Microsoft Feedback as well.
Asked by bla9x
(41 rep)
Oct 12, 2023, 01:21 AM
Last activity: Oct 18, 2023, 11:51 AM
Last activity: Oct 18, 2023, 11:51 AM