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