Retrieve AD User details for a given Active Directory User name?

  • We have Windows integrated security, and all our users are added to an appropriate Active Directory group

    that is then added as a login and user to SQL Server /DBs..

    it is rather easy to lookup AD group membership for particular users

    like EXEC master..xp_logininfo @acctname ='MyCoolestDomain\CoolestGroup', @option = 'members'

    once I run this I can see list of all individual users in group in the format it is shown on the screenshot, each row per user.. which is good.

    My question is HOW CAN I TAKE THIS  'u123456' , run a magic SELECT...  or EXEC ... and get

    AD NAME = 'Steve Jones',  DOB = '../../...',

    phone#, and other Active Directory details?

    I spent an hour browsing web for something like that, including Steve's multiple publications on the subject, and still do not get result and not sure how to do it and if it is even possible to do?

    From SSMS query specifically.

    RESULTSET OF SP

    Likes to play CHESS

  • Your looking at writing LDAP queries for that which in this day and age is something I wouldn’t do.

    I would advocate for you to use powershell and the get-ad* cmdlets to get the information from AD that you want and then get powershell to write that to the database.

    you’ll get a lot more out of powershell than you would LDAP.

  • definitely use PowerShell for this;

    here are two examples I use constantly:

    Get-ADGroupMember -Identity "CoolestGroup"  | Out-GridView

    Get-ADUser -Filter {(Name -eq 'steve.jones')} -ResultPageSize 50 -Properties * |
    Select-Object -property @{Name='AccountExpires';Expression={[DATETIME]::fromFileTime($_.accountExpires)}},
    @{n="PasswordExpirationDate";e={$_.PasswordLastSet.AddDays(90)}},PasswordNeverExpires,PasswordExpired,
    CanonicalName,sAMAccountName,ou,
    GivenName,SurName,DisplayName,email,emailaddress,
    StreetAddress,City,State,PostalCode,
    HomePhone,MobilePhone,OfficePhone,Fax,
    Company,Organization,Department,Title,Description,Office,
    extensionAttribute1,extensionAttribute2,extensionAttribute3,extensionAttribute4,extensionAttribute5,
    Enabled,PasswordLastSet,
    LastLogonDate,whenCreated,Sid | out-GridView

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Hi Lowell, I got results with your first script.

    But when I run the 2nd, replacing Steve's name with my name, I get no results (and no errors).

    I'm not familiar enough with Powershell to know what the problem might be. Different fields in A/D ??

    Do you have a simpler version I could try to run ?

    • This reply was modified 7 months, 2 weeks ago by  homebrew01.
  • sure!

    here's a handful of examples.

    you can use the filter to search for exact or like statements, using asterisk as the like character multiple domains,

     

    two of the examples have the examples where if you had a forest of AD domains to query one specific AD server (i used disney.com and sqlservercentral.local as examples)

    Get-ADUser  -Filter {samaccountname -like "*SQL*"} | Out-GridView
    Get-ADUser -Filter {samaccountname -eq "_rpt_ssrs_dev"} -Properties *

    Get-ADUser -Filter {Surname -eq "_sql_app_prd"} -Properties *
    Get-ADUser -Filter {Surname -eq "izaguirre"} -server disney.com -Properties *

    Get-ADUser -Filter {Surname -like "*"} -Properties *

    Get-ADUser -Filter {GivenName -like "*jami*"} -Properties *

    Get-ADUser -Filter {samaccountname -eq "ftorres"} -Properties *

    Get-ADUser -Filter {samaccountname -eq "jwaller2"} -Server sqlservercentral.local -Properties *

     

     

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks !  I will give them a try.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply