Using SQL Query to access Active Directory

One time I was asked to use SQL Server to access Active Directory and came across these queries that helped me.


--Setup Active Directory Linked server
--EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

--Simple Select
--select * from openquery (ADSI,'SELECT NAME FROM ''LDAP://yourdomain.com''')

--Complex Select
SELECT
samAccountName As WinNT_ID
    ,sn As Last_Name
    ,givenName As First_Name
    ,displayName AS Display_Name
,whencreated
    ,mail As Email_Address
    ,ISNULL(title, 'N/A') As Job_Title  
    ,ISNULL(telephonenumber, 'N/A') As Phone_Number
    ,ISNULL(mobile, 'N/A') As Mobile_Number
    ,ISNULL(facsimiletelephonenumber, 'N/A') As Fax_Number
    ,ISNULL(physicalDeliveryOfficeName, 'N/A') As Office
    --,ISNULL(department, 'N/A') AS Department
    ,streetAddress As Stree_Address
    ,l As City
    ,st As State
    ,postalCode As Zip_Code
--,cast(telexnumber as varchar(max))
    FROM OPENQUERY(ADSI, '
    SELECT
samAccountName, sn, givenName, title, displayName
    ,mail, telephonenumber, mobile, facsimiletelephonenumber
    ,physicalDeliveryOfficeName,l,streetAddress,st,postalCode,whencreated,telexnumber
    FROM ''LDAP://yourdomain.com'
    WHERE objectClass=''user'' AND objectClass<>''computer''
    ') AS tblADSI
WHERE sn IS NOT NULL
AND mail IS NOT NULL
--AND telexnumber IS NOT NULL
--and len(telexnumber) < 20
ORDER BY sn