Row-level Security Available for SQL Database

Earlier this week, Microsoft made Row-Level Security (RLS) available for SQL Database. This feature, which was released as a preview in January, allows the customer to control who has access to rows in their database table based on a user's characteristics such as identity, role and execution context.  The concept is similar to having  all the data is in one table and then using a query with predicates to decide what data is returned, the query doesn’t change only the values being used in the where clause changes.

This feature is implemented at database level where the predicate filtering is done by an inline table function and then enforced by a security policy. RLS works when you connect directly to the database or through an application and since it works at query time no application changes need to occur. Another benefit of the logic residing in the database is that it reduces maintenance and complexity.

If you are trying out the feature and come across the error below, then it means you are using a SQL Database V2 server.

Msg 343, Level 15, State 1, Line <n>

Unknown object type 'SECURITY' used in a CREATE, DROP, or ALTER statement.

Msg 102, Level 15, State 1, Line <n+1>

Incorrect syntax near 'FILTER'.

RLS is available in SQL Server V12 and also SQL Server 2016 CTP.

Results of MSDN sample code- Each user see only their sales and the manager see all: