Get user details form Active directory using SQL Server

Posted on 2009-02-13
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.

Question by:Andy Green
    LVL 27

    Expert Comment

    try this:

    select cn, distinguishedName, SAMAccountName
    from openquery
    (ADSI,'SELECT cn, distinguishedName, SAMAccountName
    WHERE objectCategory = ''group''
    LVL 3

    Author Comment

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

    Accepted Solution

    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://'' where objectClass = ''User'' and objectCategory=''Person'' ') ad
    inner join usermanagement.dbo.[user] u
    on u.useremail = ad.mail

    Open in new window

    LVL 27

    Expert Comment

    I used the following to create the linked server:

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

    Open in new window


    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    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 to another domain controller. Log onto the new domain controller with a user account t…
    This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now