Monday, April 4, 2011

T-SQL Recurcive stored procedure

The following T-SQL procedure gets users from Active Directory from scecified
group and populates to the tblMembersOfORG_AD_Recurcive_Groups table:

CREATE TABLE [dbo].[tblMembersOfORG_AD_Groups](
[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
,[membership] [nvarchar](500) NULL
,[loginName] [nvarchar](256) NULL
,[DateCreated] [datetime] NULL
) ON [PRIMARY]

GO

SET QUOTED_IDENTIFIER ON
GO

SET
ANSI_NULLS ON
GO

PRINT
'drop procedure dbo.udpMembersOfORG_AD_Recurcive_Groups'
GO

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

drop procedure dbo.udpMembersOfORG_AD_Recurcive_Groups
GO



PRINT
'CREATE PROC dbo.udpMembersOfORG_AD_Recurcive_Groups'
GO

/******************************************************************************
NAME: udpMembersOfORG_AD_Recurcive_Groups

PURPOSE: Gets users from Active Directory across DET from scecified

groups and populates to the tblMembersOfORG_AD_Recurcive_Groups table



REVISIONS: Call sample: exec dbo.udpMembersOfORG_AD_Recurcive_Groups 'LDAP://org.com/CN=ORG_DEPOSITOR,OU=Groups,DC=ORG,DC=com'

$Log$

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

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

CREATE PROC [dbo].[udpMembersOfORG_AD_Recurcive_Groups]
@adPath nvarchar(500)
AS

BEGIN

DECLARE @UniversalGroup nvarchar(100)
,@dcName nvarchar(100)
,@memberOf nvarchar(500)
,@strSQL varchar(7500)
,@delim CHAR(1)
,@cursorName NVARCHAR(50)
,@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)
,@err int
,@CurrentDate datetime

DECLARE @tempADSI TABLE (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))

SET @CurrentDate = getdate()
SET @delim = '/'
SET @dcName = SUBSTRING(@adPath,0,CHARINDEX('.win' + @delim, @adPath) + LEN('.win'))
SET @memberOf = SUBSTRING(@adPath,CHARINDEX('.win' + @delim, @adPath) + LEN('.win') + 1,LEN(@adPath))
SET @strSQL = 'DECLARE ADSI_cursor CURSOR
FOR SELECT sAMAccountName
,userPrincipalName
,adspath
,Department
,Title
,mail
,telephoneNumber
,sn
,givenName
,cn
FROM OPENQUERY( ADSI,'''
+ 'SELECT sAMAccountName
,userPrincipalName
,adspath
,Department
,Title
,mail
,telephoneNumber
,sn
,givenName
,cn'
+ ' FROM '''''
+ @dcName
+ ''''' WHERE memberOf=''''' + @memberOf
+ ''''''')'

-- Fill table

EXEC(@strSQL)
    
IF @@error = 0 BEGIN
OPEN ADSI_cursor
END

FETCH NEXT FROM ADSI_cursor

INTO @sAMAccountName
,@userPrincipalName
,@adspath
,@Departmen
,@Title
,@mail
,@telephoneNumber
,@sn
,@givenName
,@cn

WHILE @@FETCH_STATUS = 0 BEGIN
PRINT @adspath
IF (@sn is not null) BEGIN

BEGIN TRANSACTION
INSERT INTO dbo.tblMembersOfORG_AD_Groups(
sAMAccountName
,userPrincipalName
,adspath
,Department
,Title
,mail
,telephoneNumber
,sn
,givenName
,cn
,membership
,loginName
,DateCreated)
VALUES(@sAMAccountName
,@userPrincipalName
,@adspath
,@Department
,@Title
,@mail
,@telephoneNumber
,@sn
,@givenName
,@cn
,@adPath
,replace(substring(@adspath,0,charindex('.det.win/',@adspath)),'LDAP://','') + '\' + @sAMAccountName
,@CurrentDate)

COMMIT

END

INSERT INTO @tempADSI(sAMAccountName
,userPrincipalName
,adspath
,Department
,Title
,mail
,telephoNumber
,sn
,givenName
,cn)
VALUES(@sAMAccountName
,@userPrincipalName
,@adspath
,@Department
,@Title
,@mail
,@telephoneNumber
,@sn
,@givenName
,@cn)

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

END

IF @@error = 0 BEGIN
CLOSE ADSI_cursor
END

IF @@error = 0 BEGIN
DEALLOCATE ADSI_cursor
END

IF @@error = 0 BEGIN
DEALLOCATE DOMAIN_cursor
DECLARE DOMAIN_cursor CURSOR FOR
select sAMAccountName
,userPrincipalName
,adspath
,Department
,Title
,mail
,telephoneNumber
,sn
,givenName
,cn
from @tempADSI

END

IF @@error = 0 BEGIN
OPEN DOMAIN_cursor
END

FETCH NEXT FROM DOMAIN_cursor
INTO @sAMAccountName
,@userPrincipalName
,@adspath
,@Department
,@Title
,@mail
,@telephoneNumber
,@sn
,@givenName
,@cn

WHILE @@FETCH_STATUS = 0 BEGIN
IF (@sn is null) BEGIN

-- Recurcive call

exec dbo.udpMembersOfORG_AD_Recurcive_Groups @adspath

END

IF @@error = 0 BEGIN
FETCH NEXT FROM DOMAIN_cursor
INTO @sAMAccountName
,@userPrincipalName
,@adspath
,@Department
,@Title
,@mail
,@telephoneNumber
,@sn
,@givenName
,@cn
END

END

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