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

Question

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

VoldemarG

Hall of Fame

Points: 3883
More actions
June 3, 2021 at 12:16 am

#3891245

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.
Likes to play Chess

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

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

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.

Lowell SSC Guru Points: 323518 More actions · Answer 2

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!

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

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 3 years, 6 months ago by homebrew01.

Lowell SSC Guru Points: 323518 More actions · Answer 4

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!

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

homebrew01

SSC Guru

Points: 55548

June 4, 2021 at 3:08 pm

#3892083

Thanks ! I will give them a try.