Joining AD Query with SQL Server Query

[erinallen]

We have setup a linked server to our domain controller to get user names from AD. We first started with a view that returned all users but quickly realized the 1000 object limit.

We are now trying to narrow what is returned from AD...

We have a table in SQL Server that contains the user id. We want to join that table with AD to see the user name only for people in the table, but we can't get the where clause to work. Any ideas on how to do this?

Something like this:

SELECT
sn AS SystemUserLastName,
initials AS SystemUserMiddleInitialName,
givenName AS SystemUserFirstName,
samaccountname AS SystemUserID
FROM OPENQUERY(ADSI, 'SELECT sn, initials, givenName, samaccountname
FROM ''LDAP://OU=Users,OU=Corporate,DC=xxxx''
WHERE objectClass=''Person''
and samaccountname in (Select DPC1.OwnerName
From systest.dbo.DPC1
Where DPC1.OwnerName IS NOT NULL and DPC1.OwnerName<>'' ')

 

[mrdenny]

Getting to work is very hard. It's much easier to export the data from ad using CSVDE and them import the data into a table. This way everything is local.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com