Sample Header Ad - 728x90

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