>>>Just passing UserId and ItemId. Get back only the lowest restriction found for the Roles assigned to the user (no RoleItems entry for the Item in question implies no restriction)
>>
>>Viv,
>>
>>This better ?
>>
>>

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

>
>Restriction should be 2 for Joe :-{

Why ? He hasn't got any role