I needed a query to pull current Active Directory (AD) accounts and determine which accounts should be inactive. The main limitation is Active Directory’s 1000 object return with queries. I structured the organizational units (OU) so that different sites are in different OU’s. Structuring the data in AD this way makes it so that pulling data will be less than the 1000 object limitation for queries. I don’t want orphaned accounts in Active Directory to remain active accounts. So pulling from AD and comparing those users with users in the database help me to determine the orphaned users or users that should be inactived.
SELECT TOP 100 sAMAccountName AS Username FROM (SELECT TOP 1000 sAMAccountName FROM OpenQuery (ADSI, '<LDAP://OU=SITE,DC=CONTOSO,DC=com>;(&(&(&(objectCategory=person)(objectClass=user)(!userAccountControl:1.2.840.1135220.127.116.113:=2))(memberOf=CN=GROUPIWANT,OU=Site,DC=CONTOSO,DC=com))(|(sAMAccountName=8*)(sAMAccountName=9*)));sAMAccountName') ) AS AD WHERE sAMAccountName NOT IN ( SELECT DISTINCT ActiveUsers.Username FROM dbo.ActiveUsers WHERE ActiveUers.Active = 1 )