Get description from active directory
I'm working on importing groups and users from active directory using sql server. I've found the following code to pull both the groups and the users.
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>;(&(objectClas开发者_StackOverflow社区s=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;
That works pretty nicely. Now, i'd like to pull out the description for the groups, but i can't seem to find out what the field is called. help?
The description for groups in AD is stored in the description attribute.
Reference: Microsoft AD Schema Docs
EDIT: It seems that you can't actually pull the description attribute in using ADSI Linked Server in SQL because it is a multi-valued attribute. For an alternative way of pulling data from AD for import into SQL server, see my answer to this question.
精彩评论