0

I know that Active Directory allows for at least one object to have an assigned manager (attribute) that is the same as the current object. In other words, the employee is their own manager (i.e., CEO).

Does anyone know if it is possible to have more than one object where the manager is the same as the given object (i.e., there are co-CEO's)? Microsoft is silent on this subject (see link below).

I am extracting AD objects to a SQL server table and developing a recursive query to build the employee-to-manager hierarchy. The first step of the query gets the CEO where the DistinguishedName = ManagerDistinguishedName. The second step of the query gets all employees where the DistinguishedName <> ManagerDistinguishedName.

The query currently works where there is just one CEO for the firm. Unfortunately, I do not have a development environment for AD to test for co-CEO's and how it might impact the query. In other words, I am not sure if there is the potential for an infinite loop based on the data and query constraints.

Manager Attribute

UPDATE

;WITH EmployeeList AS (
        SELECT EmployeeObjectKey = ObjectKey, EmployeeFullName = FullName, EmployeeSamAccountName = SamAccountName, ManagerObjectKey
            ,ManagerFullName = FullName, ManagerSamAccountName = SamAccountName, EmployeeLevel = 1
        FROM #TEMP_ManagerToEmployeeHierarchy
        WHERE ObjectKey = ManagerObjectKey -- The CEO of the organization should be the only employee who's manager is themself.
        UNION ALL
        SELECT EmployeeObjectKey = E.ObjectKey, EmployeeFullName = E.FullName, EmployeeSamAccountName = E.SamAccountName, E.ManagerObjectKey
            ,ManagerFullName = M.EmployeeFullName, ManagerSamAccountName = M.EmployeeSamAccountName, EmployeeLevel = M.EmployeeLevel + 1
        FROM #TEMP_ManagerToEmployeeHierarchy E -- Employee
        INNER JOIN EmployeeList M -- Manager
            ON E.ManagerObjectKey = M.EmployeeObjectKey
            AND ObjectKey <> E.ManagerObjectKey -- Do not remove this exclusion, otherwise an infinite loop is created and the query will fail.
        )

SELECT *
FROM EmployeeList
ORDER BY EmployeeLevel, ManagerFullName, EmployeeFullName

Where:

  1. .#TEMP_ManagerToEmployeeHierarchy is a temp table that only contains active user accounts.
  2. ObjectKey is an IDENTITY value based on the order that objects are inserted. In other words, it is a translation of the objectGuid attribute.
J Weezy
  • 3,507
  • 3
  • 32
  • 88
  • @GabrielLuci By any chance, do you know the answer to this? – J Weezy May 06 '19 at 17:16
  • @J Weezy, I don't see why that would be a problem. If you are recursively go through, then it would just stop when they are their own boss as you put it. It will then return false basically and continue to loop through until no one is left and exit the loop completely. You didn't show your query, but yes it should be possible. – Matt May 07 '19 at 15:25
  • @Matt I tend to agree. But, the question still stands. Is it possible to create a circular reference? for example, AD allows for circular references in groups to be created - there is no error check for that. The result is that a lower level group can be granted access, that is only intended for a higher level group, via circular nesting. This results in an infinite loop when going through all group and nested sub-group members. I have posted the query logic. – J Weezy May 07 '19 at 15:56
  • @J Weezy Of course it would be possible to get a circular reference. Let's just go through this problem real quick. Take away the SQL query for now. If I'm checking a bunch of objects and in my object i have managerid and userid. I compare the managerid to userid, eventually i get to the point where I have no managerid and exit. That works, but... what if you did have that circular reference. I now keep going and no way to stop. Only way this would work is to keep track of where you started. like: https://stackoverflow.com/questions/50341133/recursive-base-case-for-c-circular-linked-list – Matt May 08 '19 at 04:50
  • @J Weezy I've never thought about recursion in SQL before, so not sure how to resolve that, but looks like there is an option to limit recursion https://stackoverflow.com/questions/14518090/recursive-query-in-sql-server. This should probably be the hierarchy number. You could also just put WHERE ObjectKey = CEOObjectID to start and that should give you the head reference at least. If you have Co-CEOs, it still wouldn't resolve this, so you would need a dummy person to be at the top. – Matt May 08 '19 at 04:54

1 Answers1

1

Create a dummy account, name it to the Organization Name. This can be disabled. Populate your #TEMP_ManagerToEmployeeHierarchy table with all active users and the dummy account.

Make the Organization User as the manager of CEO/CEOs/President or whatever.

Explicitly set the GUID to the org dummy account. This GUID will never change now, even if the CEO leaves.

This should solve your recursion issue since you are now tracking the head at least.

Now it should work it's way from the top and go down.

;WITH EmployeeList AS (
        SELECT EmployeeObjectKey = ObjectKey, EmployeeFullName = FullName, EmployeeSamAccountName = SamAccountName, ManagerObjectKey
            ,ManagerFullName = FullName, ManagerSamAccountName = SamAccountName, EmployeeLevel = 1
        FROM #TEMP_ManagerToEmployeeHierarchy
        WHERE ObjectKey = '{objectGuidOfTheOrganizationDummyAccount}' -- Root Node (Company name)
        UNION ALL
        SELECT EmployeeObjectKey = E.ObjectKey, EmployeeFullName = E.FullName, EmployeeSamAccountName = E.SamAccountName, E.ManagerObjectKey
            ,ManagerFullName = M.EmployeeFullName, ManagerSamAccountName = M.EmployeeSamAccountName, EmployeeLevel = M.EmployeeLevel + 1
        FROM #TEMP_ManagerToEmployeeHierarchy E -- Employee
        INNER JOIN EmployeeList M -- Manager
            ON E.ManagerObjectKey = M.EmployeeObjectKey
            AND ObjectKey <> E.ManagerObjectKey -- This should probably be remove. If there is no manager, then nothing is returned and we should be good.
        )

SELECT *
FROM EmployeeList
ORDER BY EmployeeLevel, ManagerFullName, EmployeeFullName
WHERE EmployeeLevel <> 1
Matt
  • 7,049
  • 7
  • 50
  • 77