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.

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.

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

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 🙂

 

 

BI consultant and trainer at Motion10

Leave a Reply

Next Article3 Important Lessons about Test Automation in Business Intelligence