CODEWITHSUNDEEP
sqlsql-serverrow-level-securitysecurity-policy
Murdock
Murdock

Reputation: 4662

Is SQL Server Security Policy executed per row or per select

Lets say I have

CREATE  FUNCTION [sec].[PartyGroupAccessPredicate](@PartyGroupId uniqueidentifier)
    RETURNS TABLE     
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS accessResult
        FROM [sec].[PartyGroupPartyLink] partyGroupLink
        WHERE
        (
            partyGroupLink.PartyGroupId = @PartyGroupId AND partyGroupLink.PartyId = CAST(SESSION_CONTEXT(N'PartyId') AS VARCHAR(50))
            AND @PartyGroupId IS NOT NULL
        )
        
GO



CREATE SECURITY POLICY [sec].[PartyGroupAccessPolicy] 
ADD FILTER PREDICATE [sec].[PartyGroupAccessPredicate]([PartyGroupId]) ON [int].[Program]

... would the security policy execute for each row or does sql do some optimization where its executed only once?

Is there any better approaches that would be more performant?

Upvotes: 0

Views: 139

Answers (1)

SQLZealots
SQLZealots

Reputation: 42

The security policy is applied at row level. This is how row level security is being implemented in SQL Server. Reference: Row level security Regarding, your query on performant, If you need to apply multiple filters, then it will have an impact.

Upvotes: 0

Related Questions