Extracting MS Active Directory Users

The following user defined function returns query against MS Active Directory:

USE active_directory
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + '   Server Name: ' + @@servername + '   Service Name: ' + @@servicename
GO

IF EXISTS
      (SELECT * 
          FROM sys.objects
          WHERE name = 'implementation.ADusers')
        DROP FUNCTION implementation.ADusers
        PRINT 'DROP FUNCTION implementation.ADusers'
GO

PRINT 'CREATE FUNCTION implementation.ADusers'
GO

CREATE FUNCTION implementation.ADusers()
/************************************************************************************************************************************
AD users

Usage: EXEC sp_executesql @strSQL

Change Log
 #    Date       Who  Change
*************************************************************************************************************************************/ 
 RETURNS nvarchar(max)
 BEGIN
    DECLARE @strSQL1 AS varchar(600)
    ,       @strSQL2 AS varchar(max) 
    ,       @strSQL3 AS varchar(100)
    ,       @strSQL  AS nvarchar(max) 

    SET @strSQL1 = 'SELECT samaccountname,distinguishedname,mail,mobile,telephonenumber,extensionattribute14,displayname,name,givenName,sn,useraccountcontrol,objectsid,employeeID,employeeNumber,company,title,streetAddress,postalCode,l,st,co,physicalDeliveryOfficeName,manager
    ,CAST(CASE WHEN accountexpires = 0 or accountexpires = 0x7FFFFFFFFFFFFFFF then null
                    ELSE dateadd(day, (accountexpires / (1e7 * 60 * 60 * 24)), cast(''16000101'' as datetime2))
                END AS datetime
            ) FROM (
    '

    SELECT @strSQL2 = COALESCE(@strSQL2, ' ') + alpha_union.strSQL
    FROM (
    SELECT char(decimal#) alpha
    ,'SELECT  samaccountname,distinguishedname,mail,mobile,telephonenumber,extensionattribute14,displayname,NAME,givenName,sn,useraccountcontrol,objectsid,employeeID,employeeNumber,company,title,streetAddress,postalCode,l,st,co,physicalDeliveryOfficeName,manager,accountExpires = CAST(accountExpires AS bigint) 
        FROM OPENQUERY(active_directory,''SELECT samaccountname,distinguishedname,mail,mobile,telephonenumber,extensionattribute14,displayname,name,givenName,sn,useraccountcontrol,objectsid,employeeID,employeeNumber,company,title,streetAddress,postalCode,l,st,co,physicalDeliveryOfficeName,manager,accountExpires
        FROM ''''LDAP://OU=Organisation,DC=corporate,DC=com,DC=au''''
        WHERE    objectCategory = ''''Person'''' and    objectClass = ''''USER'''' and samaccountname = '
     + '''''' + CHAR(decimal#) + '*' + ''''''''
     + IIF(CHAR(decimal#) <> 'z',' 
    ) UNION ALL 
     ','')    strSQL
    FROM (select row_number() over (order by (select 1)) as decimal#
    FROM sys.all_columns) cte_tally
    WHERE decimal# > 96 and decimal# < 123) alpha_union

    SET @strSQL3 = (SELECT ')) t    
    WHERE t.distinguishedName LIKE ''%OU=Users%''')

    SET @strSQL = @strSQL1 + @strSQL2 + @strSQL3
    RETURN @strSQL
END

If you find this script useful please donate generously.