>>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 */ >>>