query active directory

[code]
SELECT * FROM OPENQUERY(ADSI, 'SELECT name FROM 'LDAP://mydomain' WHERE objectClass='User'')

[/code
it worked just fine. but i want to select everything from active directory base on NT account (the account that user uses for their window logs in) and the user has to belong to a certain group (for instance: group = student)

now how do i do that? can you guys help. thank you

try:

Code Snippet

SELECT * FROM OPENQUERY(ADSI, 'SELECT * FROM 'LDAP://mydomain' WHERE objectClass='User'') tb

where name = suser_sname()

|||yeah but does this tell me if that user is a member of that group?|||

If you want to know if an user is a member of the group, you can use MemberOf.

e.g.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT name
FROM ''LDAP://cn=user,dc=ads''
WHERE MemberOf=''cn=,cn=users,dc=ads''
')derived

To determine all available groups, you can extract the ADsPath value.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT ADsPath
FROM ''LDAP://cn=user,dc=ads''
WHERE objectCategory=''Group''
')derived

You're better of doing all these ADSI stuffs through scripting and not via linked server.

|||what does cn, dc mean? I have to do it this way, ....doesn't want any other way to do it. so i have to follow orders.
|||

CN = Common Name

DC = Domain Content

Querying AD is a tricky business and very limited in SQL. You have to (1) have valid credential, (2) have a valid LDAP attribute.

|||

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://my_domain''
WHERE MemberOf=''My_Student''')
Go

it selected nothing and i know there are many people that belong to that group|||

You need to fully qualify your member group.

e.g.

MemberOf=''cn=My_Student,cn=Users,dc=my_domain''

If you do not have a valid object, you will just get empty resultset for ADSI linked server. This is by design.

Post the a sample of a AdsPath value here so we can tell you exactly what your cn/dc would be.

|||

this is how i get to the group in active directory; I go into teachers, then groups, then "my teacher" (group name has space between)

here is the adspath

LDAP://mydomain/CN=Carlos Lopez,OU=US Histroy,OU=History Deparemnt,DC=teacher,DC=com

the adspath below is what i got from the below code

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 objectclass=''user''
')

what i want to do is to select the user on the specific group. plz help guys i need it quick. thank you|||

Try:

MemberOf=''OU=US Histroy,OU=History Deparemnt,DC=teacher,DC=com''

To get the right object for MemberOf, you should use the ADsPath value returned from the objectCategory=''Group''.

|||i will try this today. i think i tried it b4 already. but let me try it again today.
|||still got 0 records.|||

Alright, here is the _hard_ way.

1. Use ADSI and browse to the group you want to query.

2. Right click -> select Properties.

3. Find the distinguishedName value. (e.g. CN=Domain User,CN=Users,DC=pdx,DC=com)

4. Use that exact value for your MemberOf.

e.g.

Code Snippet

select *
FROM OPENQUERY(ADSI, 'SELECT name,sAMAccountName,ADsPath
FROM ''LDAP://CN=Users,DC=pdx,DC=com''
WHERE MemberOf=''CN=Domain User,CN=Users,DC=pdx,DC=com''
') derived

Note that your LDAP will affect the resultset. It is the PARENT value for your memberOf value.

|||

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare:repare returned 0x80040e14].

that is what i got. dang it

|||

This typically refers to an invalid ldap value. At this point I suggest you contact PSS. You will need to give them the exact values to your AD (which you've obfuscated and not posted here).