Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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