declare @User table (u_id Int, u_name varchar(20)) insert into @User select 1, 'Viv' union select 2, 'Joe' declare @Role table ( r_Id int, r_name varchar(20) ) insert into @Role select 1, 'Admin' union select 2, 'User' /* Put Viv in both Admin and User Roles */ declare @UserRoles table (ur_UserId int, ur_RoleId int) insert into @UserRoles select 1,1 union select 1,2 declare @Items table (i_Id int) insert into @Items select 1 /* Restrict User role in Item 1 */ declare @RoleItems table (ri_RoleId int, ri_ItemId int, ri_Restriction int) insert into @RoleItems select 2,1,2 declare @theUser int set @theUser = 1 declare @theItem int set @theItem = 1 declare @Restriction int set @Restriction = null /* not sure this is needed */ select @Restriction = min(isnull(ri_Restriction, 0)) from @UserRoles left join @RoleItems on (ur_RoleId = ri_RoleId ) and (ri_ItemId = @theItem ) where ( ur_UserId = @theUser) select @Restriction /* 0 for theUser = 1, null for theUser = 2 */