In bigger organizations, the data in a table might be sensitive to few departments but not the others. Some people may need to rows from the table that belongs to few departments where another group of people may need to access the data belongs to another few departments in one table. Those 2 groups of people might share the data from one or few departments. Implementing such logics is not a big deal for customized applications, for instance use procedures to filter rows out. What if users access data using very genaric tools, such as SSMS, in which they can arbitrarily issue queries against table. How would you selectively return rows from a table without asking uses putting filters in their queries?

Views in SQL Server act as named queries. You can run almost all the queries in a view to perform filters, aggregates, and calculations. Views do not accept parameters. So the return set of a view cannot be adjusted by parameter(s). However, there are few ways that you can indirectly pass parameters to a view. For instance, use set context info to adjust session context information and then referece context_info() function in the view.

Returning values from system functions might be varied under different circumstances. for Instant, @@SPID will always return the current Session ID. In order satisfy the need described at very beginning, I am going to reference is_member function in views to perform the filters. This function tells whether or not current user belonging to a database role.

use tempdb
create user u1 without login
go
create role r1
go
exec sp_addrolemember 'r1', 'u1'
go
execute as user = 'u1'
go 
select IS_MEMBER('r1')
go 
revert
select IS_MEMBER('r1')

-----------
1

(1 row(s) affected)


-----------
0

(1 row(s) affected)

This is the function can be borrowed to implement row level security! Now the consideration becomes to how to create a table with ROLE information associated. This can be done by adding RoleID field to the table as an example below. An index should be created on RoleID column or Combine RoleID column and MyDataID column together as a primary key. For demo purpose, we are not considering performance matters for now.

create table MyData
(
	MyDataID int identity(1,1) primary key,
	Description varchar(100),
	RoleID int
)

In our view, we can use the RoleID column to perform filters

create view vMyData
as
select * 
from MyData
where is_member(user_name(RoleID)) = 1
go

Now let’s create 2 Roles in the database and add some records to MyData table

create role Role1;
create role Role2;
select user_id('Role1') Role1,user_id('Role2') Role2
/*Role1  Role2
------ ------
5      6

(1 row(s) affected)
*/
insert into MyData(Description, RoleID) values('value1', 6)
insert into MyData(Description, RoleID) values('value1', 5)
select * from MyData
go
/*
MyDataID    Description RoleID
----------- ----------- -----------
1           value1      6
2           value1      5

(2 row(s) affected)

*/

Now let’s retrieve the data from the view

select * from vMyData
/*
MyDataID    Description RoleID
----------- ----------- -----------

(0 row(s) affected)
*/

Nothing is returned. This is because I belongs to none of the database roles, Role1, and Role2. Now let’s create users and roles and then have a test.

grant select, insert, update on vMyData to public
create user user1 without login;
create user user2 without login;
create user user3 without login;
create user user4 without login;
create Role Role12;
exec sp_addrolemember Role12, User4

exec sp_addrolemember Role1, User1
exec sp_addrolemember Role2, User2

exec sp_addrolemember Role1, User3
exec sp_addrolemember Role2, User3

exec sp_addrolemember Role1, Role12
exec sp_addrolemember Role2, Role12
go
execute as user = 'user1'
select * from vMyData
/*
MyDataID    Description RoleID
----------- ----------- -----------
2           value1      5

(1 row(s) affected)

*/
revert
go
execute as user = 'user2'
select * from vMyData
/*
MyDataID    Description  RoleID
----------- ------------ -----------
1           value1       6

(1 row(s) affected)

*/
revert
go
execute as user = 'user3'
select * from vMyData
/*
MyDataID    Description RoleID
----------- ----------- -----------
1           value1      6
2           value1      5

(2 row(s) affected)

*/
revert
go
execute as user = 'user4'
select * from vMyData
/*
MyDataID    Description RoleID
----------- ----------- -----------
1           value1      6
2           value1      5

(2 row(s) affected)

*/
revert
go
--will they have the permission to access base table?
execute as user = 'user4'
select * from MyData
/*
Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'MyData', database 'test', schema 'dbo'.
*/
go
revert

I created 4 users. User1 can see the second row. user2 can see the first row and user3 and 4 can see both. There are 2 ways to implement row level security for a user who can see data from different departments. One is put the user into differnt Roles respectively. The second one is to create another database role, put this role into roles for difference departments, and then put user to the newly created role as the example above. Users in ths case does not have permission to the base table. I am putting view visible to everyone but it will not lead any security issues from data reading perspective.

This is for demo purpose. Accessing this view will not take any indexes due to the function, is_member, in the where clause. When MyData table becomes huge. Performance will become problematic.