>>>>>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
>
>Doh! Sorry - didn't look at this properly. I guess it does work - I'll test on proper data tomorrow.
>Thx,
>Viv


This is easier to debug - it gives all the lines that are processed
2 for user 1, no lines for user 2

select isnull(ri_Restriction, 0))
	from @UserRoles 
		left join @RoleItems 
		    on (ur_RoleId = ri_RoleId )
			and (ri_ItemId = @theItem )
		where ( ur_UserId = @theUser)