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_cursorFETCH NEXT FROM DOMAIN_cursor
   INTO @dcNameWHILE (@@FETCH_STATUS = 0) BEGINDECLARE 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