0

I am working with a SQL Server database which has row-level security implemented. To access content for a certain user, I need to append this to the SQL query:

EXEC SP_SET_SESSION_CONTEXT @key=N'UserId', @value=1

This works fine for existing tables in the database. However I have to add a new table to the database and I'm unable to add the same security to my new table.

This is how an existing table properties looks like

This is how the new table properties looks like

This is the code I found on the Microsoft docs and implemented it.

CREATE SECURITY POLICY RLS.NewTable
ADD FILTER PREDICATE RLS.fn_securitypredicate(AppUserId)   
    ON dbo.NewTable,  
ADD BLOCK PREDICATE RLS.fn_securitypredicate(AppUserId)   
    ON dbo.NewTable AFTER INSERT   
WITH (STATE = ON);

Obviously I got an error:

Cannot find the object "RLS.fn_securitypredicate" or this object is not an inline table-valued function.

Now I don't know where to find the correct block/filter predicate to be used on the new table.

TL;DR: where in a SQL Server database are the RLS predicates listed and how do I access/view them using Microsoft SQL Server Management Studio?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Aditya Srivastava
  • 17
  • 2
  • 8

1 Answers1

0

Can you confirm that you have the predicate function (RLS.fn_securitypredicate) in your database?

It will appear in Sql Server MS under -> Programmability -> Functions -> Table-valued Functions:

enter image description here

Rodney
  • 5,417
  • 7
  • 54
  • 98