In my last post, Row Level Security (2) , I talked about how to improve the performance and prevent records from being removed from the view after it’s modified. The issue was arisen in last post is how to define default values to RoleID, which is also the topic of this post.

When an user belongs to only one department, the default role of that department can be assigned. When an user is members of multiple roles, he can enter data for multiple departments but only one default role. When this user enter one a new record to the table, the default RoleID should be populated. To implement this logic, an User-Role association should be built:

create table UserDefaultRole
(
	UserID int not null primary key,
	RoleID int not null 
)

insert into UserDefaultrole(RoleID, UserID) 
	values(user_id('Role1'), user_id('User1'))
insert into UserDefaultrole(RoleID, UserID) 
	values(user_id('Role2'), user_id('User2'))
-- let's assign default role for User 3 to Role1
insert into UserDefaultrole(RoleID, UserID) 
	values(user_id('Role1'), user_id('User3'))
-- let's assign default role for User 4 to Role2
insert into UserDefaultrole(RoleID, UserID) 
	values(user_id('Role2'), user_id('User4')) 

Every user has a default RoleID now. Let’s associate this logic with the base table, Mydata, of the view

create function GetDefaultUserRole()
returns int
as
begin
	return (select RoleID from UserDefaultrole where UserID = user_id())
end
go
grant execute on dbo.GetDefaultUserRole to public
go
alter table MyData alter column RoleID int not null 
alter table dbo.MyData add constraint DefaultRoleID default dbo.GetDefaultUserRole() for RoleID
go
--I would like to recompile the view at the end since the metadata of the base table has been changed.
exec sp_recompile vMyData

Now let’s have a test

revert
delete from MyData;
go
execute as user = 'user1'
insert into vMyData(Description) values(user_name())
revert
execute as user = 'user2'
insert into vMyData(Description) values(user_name())
revert
execute as user = 'user3'
insert into vMyData(Description) values(user_name())
revert
execute as user = 'user4'
insert into vMyData(Description) values(user_name())
revert

MyDataID    Description  RoleID
----------- ------------ -----------
35          user1        5
36          user2        6
37          user3        5
38          user4        6

(4 row(s) affected)