[SQL Server] Trouver les membres d'un groupe ActiveDirectory

  • Imprimer

CREATE PROCEDURE [dbo].[usp_TrouverMembreGroupActiveDirectory]

      @LDAP_GroupActiveDirectory AS VARCHAR(500)

AS

/*

Auteur : KHARROUBI Zouhaier

Date Modification : 21 Juin 2010

Objectif : Cette procédure permet d'afficher les membres d'un groupe ActiveDirectory 

Exemple : usp_SelectMembreInGroupActiveDirectory                    @LDAP_GroupActiveDirectory='LDAP://cn=GROUPE_AD_1,cn=Users,dc=corp

,dc=kharroubi,dc=com'

usp_SelectMembreInGroupActiveDirectory @LDAP_GroupActiveDirectory='LDAP://cn=GROUPE_AD_1,cn=Users,dc=corp

,dc=kharroubi,dc=com'

*/

DECLARE @PathAD AS VARCHAR(1000)

DECLARE @SQL      AS VARCHAR(1000)

           

SELECT @PathAD = REPLACE(@LDAP_GroupActiveDirectory, 'LDAP://', '')

 

SET @SQL = 'SELECT USR.* FROM OpenQuery(SERVEUR_AD1, '

SET @SQL = @SQL +'''SELECT sAMAccountName, Adspath '

SET @SQL = @SQL +'FROM ''''LDAP://DC=corp,DC=kharroubi,DC=com'''' '

SET @SQL = @SQL +'where memberOf = '''''+ @PathAD +''''''') AD '

SET @SQL = @SQL + 'INNER JOIN ( SELECT ADsPath AS ADsPath,'

SET @SQL = @SQL + 'title AS TITRE, givenName AS PRENOM,'

SET @SQL = @SQL + 'sn AS NOM, displayName,'

SET @SQL = @SQL + 'sAMAccountName AS LOGIN, telephoneNumber AS TELEPHONE,'

SET @SQL = @SQL + 'facsimileTelephoneNumber    AS FAX, mobile AS MOBILE,UserAccountControl '

SET @SQL = @SQL + 'FROM OpenQuery(SERVEUR_AD1,'

SET @SQL = @SQL +'''SELECT ADsPath, title, displayName, sAMAccountName,'

SET @SQL = @SQL +'givenName, telephoneNumber, facsimileTelephoneNumber, sn, mobile,UserAccountControl '

SET @SQL = @SQL +'FROM ''''LDAP://DC=corp,DC=kharroubi,DC=com'''' '

SET @SQL = @SQL +'where objectClass = ''''User'''' AND objectCategory = ''''Person'''''')) USR'

SET @SQL = @SQL +' ON AD.Adspath = USR.ADsPath'

EXECUTE (@SQL)