Untitled

Get description from active directory

CREATE TABLE ##Groups ( CN VARCHAR(128), DN VARCHAR(1024), Email VARCHAR(128), ADSPath VARCHAR(1024));

CREATE TABLE ##Users ( DistributionGroup VARCHAR(128), FirstName VARCHAR(50), LastName VARCHAR(50), EmailAddress VARCHAR(128));

 

DECLARE @sql VARCHAR(1024)

 

SET @sql = 'INSERT INTO ##Groups (CN, DN, Email, ADSPath) SELECT CN, distinguishedName DN, mail Email, ADSPath FROM OpenQuery(ADSI, ''<LDAP://controller.domain.com:389/DC=domain,DC=com>;(&(objectClass=Group));cn, distinguishedName, mail, ADSPath;subtree'') ORDER BY distinguishedName';

EXEC(@sql);

 

DECLARE @CN VARCHAR(128)

DECLARE @DN VARCHAR(1024)

 

SELECT TOP 1 @CN = CN, @DN = DN FROM ##Groups

 

WHILE EXISTS(SELECT DN FROM ##Groups WHERE DN > @DN)

BEGIN

 

      SET @sql = 'INSERT INTO ##Users (DistributionGroup, Firstname, LastName, EmailAddress) SELECT '''+@CN+''' [Distribution Group], ISNULL(givenName, '''') FirstName, ISNULL(sn, '''') LastName, mail EmailAddress FROM OpenQuery(ADSI, ''<LDAP://controller.domain.com:389/DC=domain,DC=com>;(&(objectClass=User)(memberOf='+@DN+'));givenName, sn, mail;subtree'')';

      EXEC(@sql)

 

      SELECT TOP 1 @CN = CN, @DN = DN FROM ##Groups WHERE DN > @DN

END

 

SELECT * FROM ##Groups;

SELECT * FROM ##Users;

 

DROP TABLE ##Groups;

DROP TABLE ##Users;