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

Hi,
I think I finally got it right! I ended up with this SP (against my test tables so different name):

ALTER PROCEDURE [dbo].[GetRestriction] (@UserID INT, @ItemName VARCHAR(100))
AS
BEGIN
SELECT 
MIN(ISNULL(UIItemRestrictions.RestrictionEnum,0))
FROM Users
LEFT JOIN UserRoles ON Users.Id = UserRoles.UserId
LEFT JOIN UIItemRestrictions ON (UIItemRestrictions.RoleId = UserRoles.RoleId)
INNER JOIN UIItems ON (UIItems.Id = UIItemRestrictions.UIItemId) AND (UIItems.Name = @ItemName)
WHERE Users.Id = @UserId
GROUP BY Users.Id
END

Then I spent more time battling to get the SPROC working from the EntityFramework - but succeeded with that as well :-}
(Actually, looking at it now, I guess I didn't need to pull in the Users table but heck.....)