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