T-SQL script to pull Active Directory Users

This script allowed us to query AD and pull all the users into our asp_net membership system. Its a handy script and shows some real strengths of using T-SQL.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



CREATE PROCEDURE [dbo].[config_PullADUsers]
            -- Add the parameters for the stored procedure here
            @LDAProot nvarchar(MAX) 
AS
BEGIN
            -- SET NOCOUNT ON added to prevent extra result sets from
            -- interfering with SELECT statements.
            SET NOCOUNT ON;
                       
            if exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.#ldap'))
            drop table #ldap

            create table [dbo].[#ldap] (
                        [row_id] [int] IDENTITY (1, 1) NOT NULL , 
                        [userAccountControl] nvarchar(512), 
                        [objectSid] nvarchar(256), 
                        [objectSidSddl] nvarchar(256) collate Latin1_General_CI_AS_KS_WS, 
                        [samAccountName] nvarchar(256), 
                        [userPrincipalName] nvarchar(256),
                        [userPrincipalNameLower] nvarchar(256)
            )

            DECLARE @ADQLast nvarchar(MAX)
            DECLARE @ADQ nvarchar(MAX)
            SET @ADQLast = NULL

            WHILE 1=1
            BEGIN

                        IF (@ADQLast is NULL)
                        BEGIN
                                    SET @ADQ = 'SELECT userAccountControl, objectSid, samAccountName, userPrincipalName 
                                    FROM '''+@LDAProot+'''
                                    WHERE objectCategory=''user''
                                    ORDER BY samAccountName ASC'
                        END
                        ELSE
                        BEGIN
                                    SET @ADQ = 'SELECT userAccountControl, objectSid, samAccountName, userPrincipalName 
                                    FROM '''+@LDAProot+'''
                                    WHERE objectCategory=''user''
                                    AND (samAccountName > '''+@ADQLast+''')
                                    ORDER BY samAccountName ASC'
                        END

                        SET @ADQ = REPLACE(@ADQ, '''', '''''')

                        --INSERT INTO @TempA
                        --                       master.dbo.fn_sqlvarbasetostr([objectSid]), 
                        EXEC('INSERT INTO #ldap ([userAccountControl], [objectSid], [objectSidSddl],[samAccountName], [userPrincipalName], [userPrincipalNameLower]) 
                                     SELECT TOP 100 [userAccountControl],
                                     master.dbo.fn_sqlvarbasetostr(objectSid) AS objectSid, 
                                     [SigmaPlant].[dbo].[SID_hextosddl] (master.dbo.fn_sqlvarbasetostr(objectSid)) AS objectSidSddl,
                                     [samAccountName], [userPrincipalName], lower([userPrincipalName])
                                     FROM OPENQUERY(DCSERVER, '''+@ADQ+''')
                                     WHERE ((userAccountControl & 2) = 0)
                                     AND (userPrincipalName is not null)')

                        IF @@rowcount >= 100
                        BEGIN
                                    SELECT TOP 1 @ADQLast=[samAccountName] FROM #ldap ORDER BY [samAccountName] DESC
                        END
                        ELSE
                        BEGIN
                                    BREAK
                        END
            END

            DECLARE @UserCount bigint

            --SELECT * FROM #ldap ORDER BY [samAccountName] ASC
           
            SET @UserCount = (SELECT COUNT(UserId) FROM aspnet_Users)
           
            INSERT INTO aspnet_Users ([ApplicationId], [UserName], [LoweredUserName], [IsAnonymous], [Enabled], [SuperUser], [ADSID], [LastActivityDate])
            SELECT (SELECT TOP 1 [ApplicationId] FROM (SELECT DISTINCT TOP 1 [ApplicationId], COUNT([UserId]) AS UserCount FROM aspnet_Users GROUP BY [ApplicationId] ORDER BY COUNT([UserId]) DESC) A),
            [userPrincipalName], [userPrincipalNameLower], 0, 1, 0, [objectSidSddl], GETDATE() 
            FROM #ldap 
            WHERE (NOT ([objectSidSddl] IN (SELECT ADSID 
                                                                                                            FROM aspnet_Users 
                                                                                                            WHERE (ADSID IS NOT NULL))))
           
            SET @UserCount = (SELECT COUNT(UserId) FROM aspnet_Users) - @UserCount
           
            RETURN @UserCount

            --------
            --http://blog.tech-cats.com/2007/11/getting-enabled-disabled-active.html
            --SELECT samAccountName, userAccountControl, objectSid, userPrincipalName, SN, mail, ADSPath, distinguishedName
END