Row Level Security on In-Memory Tables
Published: Jul 13, 2026
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:
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:
Without this you will get an error:
Otherwise there should be no issues at all.
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
go
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)
go
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
go
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
go
create function dbo.secTestFunction(@userName varchar(10))
returns table
with schemabinding, native_compilation
as
return
(
select 1 as pass
where @userName = user_name()
)
go
create security policy secTestPolicy
add filter predicate dbo.secTestFunction(userName)
on dbo.myTestInMem
with (state = on)
go
execute as user = 'bob'
select *
from dbo.myTestInMem
revert
go
execute as user = 'fred'
select *
from dbo.myTestInMem
revert
go
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_compilationWithout this you will get an error:
Otherwise there should be no issues at all.