Recursive CTE

I was recently tasked with coming up with a stored procedure that would accept an Active Directory username, and return basically an organizational tree about that user.  This includes peers (other employees with the same manager), direct manager, and the rest of the management tree up to the CEO-level.

Now, this would probably be a task better suited for some different approach, but the requirement was to have it contained in a stored procedure that could be referenced by some other application.   Anyways, let’s get into how it works.

The way I went about this is to query Active Directory directly through a linked server.  Certainly there are other methods to get the necessary information about the users in a form accessible by SQL, and I will probably change this if/when this gets implemented.  Setting up a linked server to AD is fairly straight forward.

1. Create the linked server

   EXEC sp_addlinkedserver 
   @server = 'ADSI', 
   @srvproduct = 'Active Directory Services 2.5', 
   @provider = 'ADSDSOObject', 
   @datasrc = 'adsdatasource'

2. Assign a security context to it
3. Enable Ad HocDistributedQueriestouseOPENQUERY

   sp_configure 'show advanced options', 1 
   reconfigure
   
   sp_configure 'Ad Hoc Distributed Queries', 1 
   reconfigure

Here is the code that pulls in information about all users in a given OU:

-- Pull User information from AD linked Server into #tmpADUsers
SELECT samAccountName, 
       displayName, 
       Manager, 
       Department, 
       distinguishedname
INTO #tmpADUsers
FROM OPENQUERY(ADSI, 
'SELECT samAccountName, 
        displayName, 
        Manager, 
        Department, 
        distinguishedname 
FROM ''LDAP://OU=Manning- Napier Organization,DC=manning-napier,DC=COM'' 
WHERE objectClass=''user''AND objectClass<>''computer'' 
')

This data would probably make sense to cache on the SQL Server, maybe only updating from AD daily, but this works for a proof of concept.  From that information, I need to get information about each user’s manager.  To do this, I do a self join of the information from AD:

-- Link full manager information to users into #tmpADUsers2
SELECT usr.displayName AS [UserDisplay], 
	usr.samaccountname AS [UserSAM], 
	usr.department AS [UserDept], 
	mgr.displayname AS [ManagerDisplay], 
	mgr.samaccountname AS [ManagerSAM] 
INTO #tmpADUsers2
FROM #tmpADUsers AS [usr]
LEFT OUTER JOIN #tmpADUsers AS [mgr] ON usr.manager = mgr.distinguishedname

I store the results from this into a new temp table to use for my recursive CTE.

Now, the recursive CTE is where the magic happens.  The more common use of this is to start with the top-level (CEO) and recurse down to create a basic mapping off the entire organization, but it works the other way too.

Basically how it works is, you start with a seed row.  In my case, I seed with the username provided to the stored procedure, @USERSAM:

-- Seed CTE with information about specified user
SELECT UserDisplay, UserSAM, UserDept, ManagerDisplay, ManagerSAM, 0 AS Level
FROM #tmpADUsers2
WHERE UserSAM = @USERSAM

Now, for the recursive part.  This query gets the same user information, except for the manager of the seed user:

UNION ALL
-- Recurse up to the top-level by joining previous results on the manager's SAM
SELECT mgr.UserDisplay, mgr.UserSAM, mgr.UserDept, mgr.ManagerDisplay, 
mgr.ManagerSAM, usr.Level+1 AS Level
FROM #tmpADUsers2 mgr
INNER JOIN Emp_CTE usr ON mgr.UserSAM = usr.ManagerSAM

This part will continue until no rows are returned (when a user is reached that does not have a manager specified).  Here is the CTE.  Note the semicolon at the beginning.  This is necessary if you don’t always use semicolons to terminate statements, as the WITH CTE needs to be the first statement in a batch:

;WITH Emp_CTE AS (
	-- Seed CTE with information about specified user
	SELECT UserDisplay, UserSAM, UserDept, ManagerDisplay, ManagerSAM, 
        0 AS Level
	FROM #tmpADUsers2
	WHERE UserSAM = @USERSAM
	UNION ALL
	-- Recurse up to the top-level by joining previous results
	SELECT mgr.UserDisplay, mgr.UserSAM, mgr.UserDept, mgr.ManagerDisplay, 
        mgr.ManagerSAM, usr.Level+1 AS Level
	FROM #tmpADUsers2 mgr
	INNER JOIN Emp_CTE usr ON mgr.UserSAM = usr.ManagerSAM
)

At this point, we now have the specified user and all the management structure above them.  But what about the peers?  That is accomplished by UNIONing the results of the CTE with another SELECT query that simply uses the same temp table we referenced with the CTE to find all users with the same manager as the specified user:

-- Display results of user and management structure above them
SELECT UserDisplay, UserDept, 
CASE Level WHEN 0 THEN 'Searched' ELSE 'Manager-' + CAST(Level AS VARCHAR) END 
AS Level
FROM Emp_CTE
UNION ALL
-- Attach Peers to results
SELECT UserDisplay, UserDept, 'Peer' AS Level
FROM #tmpADUsers2
WHERE ManagerSAM = (SELECT ManagerSAM FROM #tmpADUsers2 WHERE UserSAM = @USERSAM)
AND UserSAM <> @USERSAM
ORDER BY Level, UserDisplay

And that is pretty much all there is to it.  I like to clean up a little at the end by dropping my temp tables, then I wrapped the whole thing in a stored procedure.  The full script to create a temporary stored procedure can be found here: ScriptCenter GitHub