SQL Server 2016 – Row Level Security (RLS)

 Sep 09, 2016

Hello again friends, I'm glad you could join me for another foray into the exciting world of SQL Server 2016.

I'd like to have a brief chat with you today about another of the new features in SQL Server 2016. That is, Row-level Security or RLS.

This is a very handy little idea. Have you ever attempted to enable access to different rows within a table to different people? You need to determine the identity of the user searching the data then filter based on that info. Turns out, that's not as easy as you would imagine.

Microsoft have decided to address that very issue and make your life vastly easier in the process. So Adam, I hear you ask, what did they come up with? Well, it's rather ingenious. It involves using a function and the new security policy feature that's being rolled out with SQL Server 2016. The combination of those two means that, with no further interference, you can secure the rows that pertain to each particular individual.

Honestly there's nothing better than an example to show you what I mean.

So I'll start by creating three user accounts that we'll use to demonstrate the capability.

Great, easy so far, yeah? Next step, I want to create a table to access:

What a lovely table. Simple AND elegant. Let's put some stuff in it shall we?

And let's have a look at the newly inserted rows; See! Nothing up my sleeve!:


And, of course, we're going to need to grant some permissions on this new data, so:

Now, here's where the magic begins. Firstly we'll create an inline table valued function which, in our example, will simply return a 1 if a row in the SalesRep column of the Sales table matches the user executing the query, or if the user executing the query is the Manager. Easy, peasy, Campese!

Of course, I'll also need to create one of these funky new security policies as well:

And now, we're finally ready to jump in and test it. Woohoo!

With this result:


With this result:

And:

With this result:

And, really, we should test that it works when we turn the security policy off:

And the result when executed as me, Student:

Brilliant

Alright, I hope you've enjoyed this small demonstration into the utility of the new Row-Level Security (RLS) that was introduced in SQL Server 2016.

Of course, if you come along to a course you'll find out heaps more about not only RLS but many other topics. Have a look at our brilliant website for a list of all the SQL Server 2016 courses on offer, there are 12 of them to pick from, even the fussiest DBA will find something of interest I'm sure.

Thanks for stopping by, I hope you've found this brief blog about Row-Level Security enlightening. See you again soon.

Cheers – Adam

Make sure to have a look at our SQL Server 2016 training courses.

How do your Excel skills stack up?   

Test Now  

About the Author:

Adam Keats  

With over 25 years of real-world IT experience Adam is one of New Horizons’ most senior Database and Software Development trainers. Starting out as a physics teacher Adam displayed exceptional mathematical and technical capabilities early on in his career. He went on to work in a variety of programming and technical management roles within several government agencies including the Department of Defence and Maritime Patrol. In 1998 Adam found his true calling, gaining MCT status and thus beginning his technical training career specialising in SQL Server administration, development, BI, and .NET development. Since then he has worked for several training organisations and found a home at New Horizons where he is now our resident Database and Development specialist. Throughout his tenure at New Horizons, Adam has assisted over 500 students in their endeavours to improve their skills, knowledge, and to achieve industry certifications.

top