Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Get user details form Active directory using SQL Server

Posted on 2009-02-13
4
Medium Priority
?
693 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
0
Comment
Question by:Andy Green
  • 3
4 Comments
 
LVL 27

Expert Comment

by:nmarun
ID: 23632523
try this:

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

Author Comment

by:Andy Green
ID: 23632736
What do I join to? sould I be able to alias the  'OPENQUERY' to expose at able name
Andy
0
 
LVL 27

Accepted Solution

by:
nmarun earned 2000 total points
ID: 23633985
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

0
 
LVL 27

Expert Comment

by:nmarun
ID: 23634120
I used the following to create the linked server:

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

Open in new window

0

Featured Post

Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A hard and fast method for reducing Active Directory Administrators members.
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question