query active directory

dummies2 · August 20th, 2007, 12:16 PM

im trying to select all the users that has a login to their machine at school, and these people have to be in a certain group ,in active directory. But i cant do it. If i select only user it works fine, but as soon as i put memberof='groupname' it gave back nothing. This is what i got

Code:

CREATE view View1
AS
select sAMAccountname, [name], physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath from openquery
(
ADSI,'SELECT sAMAccountName, name, physicalDeliveryOfficeName, givenName, userPrincipalName, cn, sn, mail, adsPath
FROM ''LDAP://mydomain''
WHERE objclass = ''user'' and memberOf='mystudent''
')
Go
select * from view1 order by adspath

drop view view1

the code above returns no records.

if i take out memberof i got many records.

if i put in

Code:

i got this code below from this site :http://technet2.microsoft.com/window....mspx?mfr=true

memberOf=''CN=...,OU...,DC...,DC....

then i got this error message
Server: Msg 7321, Level 16, State 2, Procedure View1, Line 3
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare returned 0x80040e14].

can some on help me plz plz plz.

Peso · August 20th, 2007, 12:50 PM

Try to put multiple single quote around the ''groupname'''
Single quote is used as escape character for string in SQL Server.

dummies2 · August 20th, 2007, 01:00 PM

didnt work either it gave me that error just like b4 about preparing.

joefawcett · August 21st, 2007, 02:12 AM

Can you show the query as it stands now?

--

Joe (Microsoft MVP - XML)

dummies2 · August 21st, 2007, 08:33 AM

huh? i did on the every first message the whole query is there.