This is going to be a long answer to clarify what we're doing.
TL;DR version
Use recursive CTE with bottom-up strategy and view that presents hierarchy of roles. Go to the last code snippet for solution.
LIVE DEMO - click here
FULL VERSION
You could use a recursive CTE to query hierarchical data bottom-up - from the leaf to the node (here from areas to divisions, but in your real example just add teams). Moreover, since you have separate tables for each level we're going to simplify it using a view that will keep the entire tree and will allow us to actually build a recursive CTE query. More on that later. Let's start.
Preparing simplified data sample
CREATE TABLE divisions ( divisionid int );
CREATE TABLE departments ( departmentid int, divisionid int);
CREATE TABLE areas ( areaid int, departmentid int);
CREATE TABLE userroles ( userid varchar(255), unitid int, unittype varchar(255));
INSERT INTO divisions VALUES (1),(2);
INSERT INTO departments VALUES (1,1),(2,2);
INSERT INTO areas VALUES (1,1),(2,1),(3,3),(4,2);
INSERT INTO userroles VALUES ('smithj',1,'Division'),('doej',1,'Department'),('simpsonh',2,'Area'),('anyother',1,'Area'),('smhg',1,'Division');
Let's see how userroles table looks like:
+----------+--------+------------+
| userid | unitid | unittype |
+----------+--------+------------+
| smithj | 1 | Division |
| doej | 1 | Department |
| simpsonh | 2 | Area |
| anyother | 1 | Area |
| smhg | 1 | Division |
+----------+--------+------------+
Now we actually go a step ahead to CREATE VIEW, because since we have different levels in separate tables the logic would require to use couple of LEFT JOINs and in SQL Server we can't use these in recursive CTE query. This error would pop up:
Outer join is not allowed in the recursive part of a recursive common
table expression
So we're creating the roles hierarchy view to convert left joins logic into one table that we can INNER JOIN to:
CREATE VIEW roles AS (
SELECT 'Division' AS unittype, divisionid as unitid, cast(null as int) as parentid, null as parenttype FROM divisions
UNION ALL
SELECT 'Department', departmentid, divisionid, 'Division' FROM departments
UNION ALL
SELECT 'Area', areaid, departmentid, 'Department' FROM areas
);
Let's see what our view roles presents:
+------------+--------+----------+------------+
| unittype | unitid | parentid | parenttype |
+------------+--------+----------+------------+
| Division | 1 | NULL | NULL |
| Division | 2 | NULL | NULL |
| Department | 1 | 1 | Division |
| Department | 2 | 2 | Division |
| Area | 1 | 1 | Department |
| Area | 2 | 1 | Department |
| Area | 3 | 3 | Department |
| Area | 4 | 2 | Department |
+------------+--------+----------+------------+
So far so good. Let's set two variables for search criteria and write our final query. I'm choosing unittype = 'Area' and unitid = 2. This will make the expected hierarchy go this way:
(Area: 2) -> (Department: 1) -> (Division: 1)
Which should bring in results users assigned responsibility for these unit types:
simpsonh <- from Area 2
doej <- from Department 1
smhg <- from Division 1
smithj <- from Division 1
Now to the query. At first we're picking up our unit type and id of choice (through set variables) in the roles view so that we already have the first parent to start going through the hierarchy. We're also joining to userroles table to find any user that may be directly responsible for the unit we're investigating. We use LEFT JOIN because there may be nobody that has a direct responsibility at this level (in the example: Area). Then we're doing the recursive part by joining roles and userroles back to our result from previous query (cte) to look for users that are responsible for direct parent. In our case for Area this would be looking for Department and then for Division.
At last we're discarding any rows where userid IS NULL because we don't want to show rows that appeared from first level if nobody had direct responsibility. In our case they do, but if you remove user assigned to (Area, 2) you'll see how it works:
SQL solution code
-- If you already have data in your tables and created view, proceed with this code:
DECLARE @unittype varchar(max);
DECLARE @unitid int;
SET @unittype = 'Area'; -- change it to look for what you want
SET @unitid = 2; -- change it to look for what you want
WITH cte AS (
SELECT ur.userid, r.unittype, r.unitid, r.parenttype, r.parentid
FROM roles r
LEFT JOIN userroles ur ON ur.unitid = r.unitid AND ur.unittype = r.unittype
WHERE r.unittype = @unittype AND r.unitid = @unitid
UNION ALL
SELECT ur.userid, r.unittype, r.unitid, r.parenttype, r.parentid
FROM roles r
INNER JOIN cte c ON r.unitid = c.parentid AND r.unittype = c.parenttype
INNER JOIN userroles ur ON ur.unittype = r.unittype AND ur.unitid = r.unitid
)
SELECT userid
FROM cte
WHERE userid IS NOT NULL
