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
   
   Create Linked Server

   Transact-SQL

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

   1 2 3 4 5

   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
   
   Reconfigure

   Transact-SQL

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

   1 2 3 4 5

   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:

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:

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:

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

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:

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:

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