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