Row level security on your SQL database

One of the cool new features of SQL 2016 or SQL Azure is that you can add row level security to your database directly.

So you don't need to use a cube or a tabular model to implement row level security. Let me show you how you can use it. The example is based on Adventureworks, the sample database from Microsoft. In this example I am going to show how you can restrict acces to certain colors of products (not really a realistic scenario I guess, but it's done pure as an example).

First we are going to create a table where we store for each user what colors he can see.

CREATE TABLE [dbo].[UserRights](
[login] [nvarchar](100) NULL, 
[color] [nvarchar](15) NULL)

second, we are going to create a function that checks wether a user can see a certain color:

Make sure to add yourself to the userrights table, or add an exception in the function below to make sure certain roles can see al colours regardless.

Create Function dbo.fn_securitypredicateColor (@color nvarchar(15))
returns table
with Schemabinding
return select 1 as [fn_securityPredicateColorresult]
from  dbo.userrights rr
where( rr.login = SUSER_name() 
and rr.color = @color

and lastly, we need to enable row level security on the products table:

CREATE SECURITY POLICY [dbo].[fn_security]
    ADD FILTER PREDICATE dbo.fn_securitypredicateColor([color]) ON [SalesLT].[Product]
    WITH (STATE = ON);

There you go!

You may want to restrict acces based on roles rather than users, but you can build from this example and try it out yourself 🙂



Principal BI consultant at Rubicon

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Next Article3 Important Lessons about Test Automation in Business Intelligence