Row Level Security on In-Memory Tables
Published: Nov 28, 2021
Quick note to combine the last few sets of posts… can you use Row Level Security on in-memory tables?

We all know that in-memory tables do tend to have a lot of quirks and many things we might want to use don’t seem to, but in this case we’re all good… row level security does in fact work on in-memory tables:

drop table if exists dbo.myTestInMem

create table dbo.myTestInMem
id int identity not null primary key nonclustered,
userName varchar(10) not null,
myData int,
index ix_mem nonclustered hash(userName) with(bucket_count = 10)
with (memory_optimized = on)

insert into dbo.myTestInMem select 'bob', 1
insert into dbo.myTestInMem select 'bob', 2
insert into dbo.myTestInMem select 'fred', 3
insert into dbo.myTestInMem select 'fred', 4
insert into dbo.myTestInMem select 'bob', 5

if exists (select * from sys.sysusers where name = 'bob')
drop user bob;

if exists (select * from sys.sysusers where name = 'fred')
drop user fred;

create user bob without login
create user fred without login;

grant select on dbo.myTestInMem to bob
grant select on dbo.myTestInMem to fred;

if exists(select * from sys.security_policies where name = 'secTestPolicy')
drop security policy secTestPolicy;

drop function if exists dbo.secTestFunction

create function dbo.secTestFunction(@userName varchar(10))
returns table
schemabinding, native_compilation
select 1 as pass
where @userName = user_name()

create security policy secTestPolicy
add filter predicate dbo.secTestFunction(userName)
on dbo.myTestInMem
with (state = on)

execute as user = 'bob'

select *
from dbo.myTestInMem


execute as user = 'fred'

select *
from dbo.myTestInMem


As you can see, it’s exactly as we expected.

There is one thing to note though, and that’s the fact that you MUST natively compile your security function in order to use it with an in-memory table. Therefore you will notice that the function states:

with schemabinding, native_compilation

Without this you will get an error:

Otherwise there should be no issues at all.

NB: Comments will only appear once they have been moderated.