Thursday, March 31, 2011

Extract user details from Active Directory and Store it in the database table

The following T-SQL stored procedure extracts user details from Active Directory and stores it in the database table:

CREATE TABLE [dbo].[tblORG_AD_Users](
[sAMAccountName] [nvarchar](256) NULL
,[userPrincipalName] [nvarchar](256) NULL
,[adspath] [nvarchar](400) NOT NULL
,[Department] [nvarchar](256) NULL
,[Title] [nvarchar](256) NULL
,[mail] [nvarchar](256) NULL
,[telephoneNumber] [nvarchar](256) NULL
,[sn] [nvarchar](256) NULL
,[givenName] [nvarchar](256) NULL
,[cn] [nvarchar](256) NULL
,[l] [nvarchar](256) NULL
,[streetAddress] [nvarchar](256) NULL
,[userAccountControl] [int] NULL
,[loginName] [nvarchar](256) NULL
,[DateCreated] [datetime] NULL
) ON [PRIMARY]

GO

/****** Create Object: LinkedServer [adsi] ******/

EXEC master.dbo.sp_addlinkedserver @server = N'adsi', @provider=N'ADSDSOObject'
GO

SET
QUOTED_IDENTIFIER ON
GO

SET
ANSI_NULLS ON
GO

PRINT
'drop procedure dbo.udpGet_ORG_LDAP_Users'
GO

if
exists (select * from dbo.sysobjects
where id = object_id(N'dbo.udpGet_ORG_LDAP_Users')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure dbo.udpGet_ORG_LDAP_Users
GO

PRINT
'CREATE PROC dbo.udpGet_ORG_LDAP_Users'
GO

/******************************************************************************
NAME: udpGet_ORG_LDAP_Users

PURPOSE: Gets users from Active Directory across DET and populates to

the tblORG_AD_Users table

PARAMETERS: @pdcName - Domain (e.g 'org.com.win')

CALL SAMPLE: exec dbo.udpGet_ORG_LDAP_Users null

NOTE: userAccountControl can be use to filter enabled account
(use WHERE userAccountControl & 2 = 0 clause in the query)



REVISIONS:

$Log$

Ver Date Author Description
--------- ---------- --------------- ------------------------------------

******************************************************************************/

CREATE PROC dbo.udpGet_ORG_LDAP_Users
@pdcName nvarchar
AS

BEGIN

DECLARE @sAMAccountName nvarchar(256)
,@userPrincipalName nvarchar(256)
,@adspath nvarchar(400)
,@Department nvarchar(256)
,@Title nvarchar(256)
,@mail nvarchar(256)
,@telephoneNumber nvarchar(256)
,@sn nvarchar(256)
,@givenName nvarchar(256)
,@cn nvarchar(256)
,@l nvarchar(256)
,@streetAddress nvarchar(256)
,@userAccountControl int
,@strSQL varchar(7500)
,@dcName varchar(100)
,@ABC VARCHAR(1)
,@CurrentDate datetime

SET @CurrentDate = getdate()

TRUNCATE TABLE dbo.tblORG_AD_Users

SET @strSQL = 'DECLARE DOMAIN_cursor CURSOR FOR
SELECT name
FROM OPENQUERY( ADSI,'''
+ 'SELECT name
FROM ''''LDAP://org''''''' + ')'
+ ' WHERE name like ''%.com.win'''

IF (@pdcName is not null) BEGIN
SET @strSQL = @strSQL + ' AND name = ''' + @pdcName + ''''
END

SET @strSQL = @strSQL + ' GROUP BY name'
SET @strSQL = @strSQL + ' ORDER BY name'

EXEC(@strSQL)

OPEN DOMAIN_cursor

FETCH NEXT FROM DOMAIN_cursor
INTO @dcName

WHILE (@@FETCH_STATUS = 0) BEGIN

DECLARE ABC_cursor CURSOR FOR
SELECT 'A'
UNION
SELECT 'B'
UNION
SELECT 'C'
UNION
SELECT 'D'
UNION
SELECT 'E'
UNION
SELECT 'F'
UNION
SELECT 'G'
UNION
SELECT 'H'
UNION
SELECT 'I'
UNION
SELECT 'J'
UNION
SELECT 'K'
UNION
SELECT 'L'
UNION
SELECT 'M'
UNION
SELECT 'N'
UNION
SELECT 'O'
UNION
SELECT 'P'
UNION
SELECT 'Q'
UNION
SELECT 'R'
UNION
SELECT 'S'
UNION
SELECT 'T'
UNION
SELECT 'U'
UNION
SELECT 'V'
UNION
SELECT 'W'
UNION
SELECT 'X'
UNION
SELECT 'Y'
UNION
SELECT 'Z'

-- Get Alphabet letter

OPEN ABC_cursor

FETCH NEXT FROM ABC_cursor
INTO @ABC

WHILE (@@FETCH_STATUS = 0) BEGIN

SET @strSQL = 'DECLARE ADSI_cursor CURSOR
FOR SELECT sAMAccountName
,userPrincipalName
,adspath
,Department
,Title
,mail
,telephoneNumber
,sn
,givenName
,cn
,l
,streetAddress
,userAccountControl
FROM OPENQUERY( ADSI,'''
+ 'SELECT sAMAccountName
,userPrincipalName
,adspath
,Department
,Title
,mail
,telephoneNumber
,sn
,givenName
,cn
,l
,streetAddress
,userAccountControl'
+ ' FROM ''''LDAP://'
+ @dcName + ''''''
+ ' WHERE objectClass=''''user'''''
+ ' AND objectCategory=''''Person'''''
+ ' AND mail=''''*'''''
+ ' AND sn=''''' + @ABC + '*'''''''
+ ')'

EXEC(@strSQL)

-- Fill table

OPEN ADSI_cursor

FETCH NEXT FROM ADSI_cursor
INTO @sAMAccountName
,@userPrincipalName
,@adspath
,@Department
,@Title
,@mail
,@telephoneNumber
,@sn
,@givenName
,@cn
,@l
,@streetAddress
,@userAccountControl

INSERT INTO dbo.tblORG_AD_Users(
sAMAccountName
,userPrincipalName
,adspath
,Department
,Title
,mail
,telephoneNumber
,sn
,givenName
,cn
,l
,streetAddress
,userAccountControl
,loginName
,DateCreated)
VALUES( @sAMAccountName
,@userPrincipalName
,@adspath
,@Department
,@Title
,@mail
,@telephoneNumber
,@sn
,@givenName
,@cn
,@l
,@streetAddress
,@userAccountControl
,replace(substring(@adspath,0,charindex('.org.win/',@adspath)),'LDAP://','') + '\' + @sAMAccountName
,@CurrentDate)

WHILE @@FETCH_STATUS = 0 BEGIN

FETCH NEXT FROM ADSI_cursor
INTO @sAMAccountName
,@userPrincipalName
,@adspath
,@Department
,@Title
,@mail
,@telephoneNumber
,@sn
,@givenName
,@cn
,@l
,@streetAddress
,@userAccountControl

INSERT INTO dbo.tblORG_AD_Users(
sAMAccountName
,userPrincipalName
,adspath
,Department
,Title
,mail
,telephoneNumber
,sn
,givenName
,cn
,l
,streetAddress
,userAccountControl
,loginName
,DateCreated)
VALUES( @sAMAccountName
,@userPrincipalName
,@adspath
,@Department
,@Title
,@mail
,@telephoneNumber
,@sn
,@givenName
,@cn
,@l
,@streetAddress
,@userAccountControl
,replace(substring(@adspath,0,charindex('.det.win/',@adspath)),'LDAP://','') + '\' + @sAMAccountName
,@CurrentDate)

END

CLOSE ADSI_cursor

DEALLOCATE ADSI_cursor


FETCH NEXT FROM ABC_cursor
INTO @ABC

END

CLOSE ABC_cursor

DEALLOCATE ABC_cursor

FETCH NEXT FROM DOMAIN_cursor
INTO @dcName


END

CLOSE DOMAIN_cursor

DEALLOCATE DOMAIN_cursor

END



GO

SET
QUOTED_IDENTIFIER OFF
GO

SET
ANSI_NULLS ON
GO

If you find this script useful please donate generously.

No comments:

Post a Comment