I was recently working with an AX 2009 customer who wanted to compare the user accounts configured in AX with the user accounts in Active Directory. The basic goals were:
- Find all AX user accounts that no longer exist in Active Directory.
- Find all accounts that are disabled in Active Directory but not in AX.
It would be great if AX would flag these scenarios for you, but unfortunately it doesn’t. If you’re interested in knowing if you have any orphaned accounts or accounts that should probably be disabled in AX, here’s a quick way to do just that.
- Export AD users to a CSV file. I used a PowerShell command for this step. The command I used requires the Active Directory Module for Windows PowerShell. This is installed by default on domain controllers, but it is also available via the Remote Server Administration Tools for Windows 7 if you want to run it from a workstation instead. http://www.microsoft.com/download/en/details.aspx?id=7887
- Create a table for the AD user details. I created a new table in the AX database to store the AD user account details so I could easily join this information to the AX user details already stored in the database.
- Load the contents of the CSV file into the table. I used a bulk insert statement to load the data from the CSV file created in step 1 into the table created in step 2.
- Query the table for your results. I used 2 simple queries that joined the AD user account table with the AX userinfo table to get the information I needed.
NOTE: See the attached text file for the exact PowerShell commands and SQL statements I used.
In the one real world scenario (AX 2009) that we looked at, AX had 112 orphaned accounts and there were another 75 accounts that were disabled in AD but not in AX.
This procedure should work for both AX 4.0 and 2009. The userinfo table still exists in AX 2012, so the comparison should work with this version too, but there might be some scenarios such as flexible authentication that throw the results off. That’s something I haven’t really looked into yet.