>>>>
>>>>
>>>>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.....)
>>
>>
>>(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)
>
>Hi,
>
>I think that's acceptable - I'm only mapping restrictions. If the user doesn't belong to any role then there should be no restrictions.
>
>I know I'm trying to do this a**-backwards. Most schemes rely on granting permissions but in this instance fields with a restriction will be greatly in the minority so I thought it might be better this way rather than having to explicitly grant permissions.....
>
>Of course I could swap things around and use the rule 'If the item IS in the Items table then permission has to be explicitly granted' but then there would still be a lot more entries in the equivalent Roles-Items link table
>
>Oh - and I dropped the inclusion of the Users table without problems.....


The odds are that you'll probably end up loving sql