[SQL Server] Trouver les membres d'un groupe ActiveDirectory
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)