We help IT Professionals succeed at work.

Get user details form Active directory using SQL Server

Medium Priority
714 Views
Last Modified: 2012-05-06
Hi All

I've set up my linked server, and it seems to work ok. My question is given a users login ID from my database, how do I join to the AD and get their username / role etc.

Andy
Comment
Watch Question

Top Expert 2009

Commented:
try this:

select cn, distinguishedName, SAMAccountName
from openquery
(ADSI,'SELECT cn, distinguishedName, SAMAccountName
FROM ''LDAP://NGMNA1DCG
WHERE objectCategory = ''group''
')

Author

Commented:
What do I join to? sould I be able to alias the  'OPENQUERY' to expose at able name
Andy
Top Expert 2009
Commented:
I got it..

I have a user table and I'm joining the useremail column to the mail field in AD.

select cn, mail, distinguishedname, displayname, samaccountname  FROM OPENROWSET('ADSDSOObject',
 'adsdatasource;', 'SELECT  cn, mail, distinguishedName, displayName, samaccountname
 FROM ''LDAP://COMPANY.com'' where objectClass = ''User'' and objectCategory=''Person'' ') ad
inner join usermanagement.dbo.[user] u
on u.useremail = ad.mail

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Top Expert 2009

Commented:
I used the following to create the linked server:

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 
'ADSDSOObject', 'ADServerName'

Open in new window

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.