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