I am always trying to remember the exact format for querying active directory using T-SQL, so I decided to document it here for posterity’s sake. If any one else is interested in using this code, our domain is na.company.com, so you would just have to change the “DC=” statements to match your company’s domain.
SELECT [name], company, department, mail
FROM OpenQuery( ADSI, 'SELECT name, company, department, mail, adsPath FROM ''LDAP://DC=na,DC=company,DC=com'' WHERE objectCategory=''Person'' AND objectClass=''user''')
WHERE department IS NOT NULL
ORDER BY name
