Hi Erland

I though about the union, but went for the simpler model as I think
maintainance would be easier, after all there is always the superset! I also
missed out the possible need to use DISTINCT if users were in multiple
groups with access to the same menu.

John


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns95C3BFD1DEFDDYazorman@127.0.0.1...
> John Bell (jbellnewsposts@hotmail.com) writes:
>> It would be simpler if you dispensed with granting access to an
>> individual
>> then it would be one query joining the menu, menugrpaccess and usr2grp
>> tables filtering on the userID in the usr2grp table, something like:
>>
>> SELECT M.MenuId, M.Name
>> FROM Menu M
>> JOIN MenuGrpAccess A ON A.MenuId = M.MenuId
>> JOIN Usr2Grp G ON G.GroupId = A.GroupId
>> WHERE G.UserId = @UserId
>>
>> This does assume that you do not have groups within groups (not another
>> hierarchy!!!!!) .
>
> As long as there are not groups within groups, there is no major problem
> with direct access for users.
>
> SELECT M.MenuId, M.Name
> FROM Menu M
> JOIN MenuGrpAccess A ON A.MenuId = M.MenuId
> JOIN Usr2Grp G ON G.GroupId = A.GroupId
> WHERE G.UserId = @UserId
> UNION
> SELECT M.MenuID, M.name
> FROM Menu M
> JOIN MenuUserAccess a ON A.MenuID = M.MenuId
> WHERE A.UserID = @UserID
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp