>>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.....)
>
>
>(1) No, I do not think you need the Users table
>Just select from UserRoles where ( UserRoles.UserId = @UserId )
>
>(2) What results do you get if there's a user without any entry in the UserRoles ? (Remember Joe ?)
>I covered that a couple of posts back
>
>UPDATE
>
>Test carefully the user without entry in the UserRoles table
>I look at this
>>LEFT JOIN UserRoles ON Users.Id = UserRoles.UserId
>and I think it will end up as 0 = no restriction (due to the outer join)
>
>
>Cheers,


Actually, on the second thought - you're right, we don't need Users table.