• Thanks guys, all good points.

    I think the kicker here is that a user may have access to departments and/or offices and/or regions. It's the "ORs" that are killing the query.

    I like the approach of three tables, but I can't do inner joins since a user may have access to some offices, but not to any regions. (I must also note that users are different than employees, so Sean's first query above would need some minor adjustment). I understand about normalization, but if I store an ID and the type of ID separately, then I lose referential integrity on the ID column, so I don't think that's a viable option.

    HOWEVER!!! The union is interesting, and my prelim tests seem to be resoundingly successful.

    My really big challenge here is that there are over 100 stored procs already written that do not yet filter based on this defined user access (the user access is a new business requirement after 10 years!) and so whatever we decide as a solution will need to be incorporated into them. We'll pass the requesting user's ID to the procs, and then process the data using the user access filtering.

    So maybe I create a temp table or a CTE using Sean's union approach, and then do an inner join against that temp table/CTE within each of the queries. Something like this, maybe:

    WITH Emps AS (

    SELECT EmployeeID

    FROM Employees e

    JOIN UserAccess a ON e.DepartmentID = a.DepartmentID

    WHERE a.UserID = @user-id

    UNION ALL

    SELECT EmployeeID

    FROM Employees e

    JOIN UserAccess a ON e.OfficeID = a.OfficeID

    WHERE a.UserID = @user-id

    UNION ALL

    SELECT EmployeeID

    FROM Employees e

    JOIN UserAccess a ON e.RegionID = a.RegionID

    WHERE a.UserID = @user-id

    )

    SELECT *

    FROM Employees e

    JOIN Emps x ON e.EmployeeID = x.EmployeeID

    And yes indexing will certainly play a part.... Now I just have to figure out how to allow users with no access defined to be able to access all the employees. Maybe add a "AND WHERE EXISTS (SELECT * FROM Emps)" to the join.