Querying Active Directory

[DBAWinnipeg]

IF NOT EXISTS (SELECT srvname FROM master..sysservers WHERE srvname = 'ADSI')
BEGIN
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
END

DECLARE @SQL_STMT as nvarchar(300)
DECLARE @LOGIN_NAME as varchar(30)

SET @LOGIN_NAME = 'MyName'


SET @SQL_STMT = 'SELECT [name] as ' + CHAR(39) + 'AD_Login' + CHAR(39) + ',' +
' UPPER([displayname]) as ' + CHAR(39) + 'AD_Display_Name' + CHAR(39) +
' FROM OpenQuery(ADSI, ' + CHAR(39) + ' SELECT name, displayname ' +
' FROM ' + CHAR(39) + CHAR(39) + 'LDAP://DOMAIN.ca/OU=Accounts,DC=DOMAIN,DC=ca' +
CHAR(39) + CHAR(39) + ' WHERE name = ' + CHAR(39) + CHAR(39) + @LOGIN_NAME +
CHAR(39) + CHAR(39) + CHAR(39) + ')'
PRINT @SQL_STMT
EXEC sp_executesql @SQL_STMT


-------------------------------------
-------------------------------------

OK This query above takes the variable @LOGIN_NAME and returns me information about that login from active directory.

Here's the $5,000,000 question:

Can I query Active Directory, supply the group name and return all the users in that group??

Please help!!





Thanks in advance!!!

Colin in da 'Peg

 

[DBAWinnipeg]

To expand on this a little bit let me tell what I'm trying to accomplish.

The goal here is to be able to pull First and Last Name information for accounts that can login to SQL Server.

(Just in case there is another way to do this)


Thanks again


Thanks in advance!!!

Colin in da 'Peg

 

[DBAWinnipeg]

SELECT [name] as 'AD_Login', UPPER([displayname]) as 'AD_Display_Name'
FROM OpenQuery(ADSI, ' SELECT name, displayname FROM ''LDAP://domain.ca/OU=Accounts,DC=domain,DC=ca''
WHERE objectClass = ''user'' AND memberOf =''CN=ISTM,OU=Groups,OU=Accounts,DC=domain,DC=ca ''
' )


OK this query returns results 26 rows for the group ISTM.... BUT the ISTM group has over 100 accounts? Does anyone have any idea why it is only returning me 26 rows??



Thanks in advance!!!

Colin in da 'Peg