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

Tags: Blogger T-SQL Code

Published: 2008-07-23