Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Query ActiveDirect for username from computer name

Posted on 2013-02-04
Medium Priority
Last Modified: 2013-04-02
I need to get the network/"logged on" username from Active Directory, using the computer name that a user is logged on with. Would like to use VB/VBA or VBScript to do this.
The scenario is:
I can get the computer name for users that have a Microsoft Access database file open by examining the lockfile associated with it. But I can't get the username so that I can contact them to ask them to close the database so I can update the file. I've tried using the WMI Service object but it's not working, apparently, due to a permissions issue (it returns the error, "permission denied"). If I have the username, I can contact the user from our Outlook directory.
Question by:Concentus
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 4
  • +2
LVL 22

Expert Comment

by:Joseph Moody
ID: 38850864
Set this script up as a logon script:

Set objSysInfo = CreateObject("ADSystemInfo") 

On Error Resume Next
Set objComputer = GetObject("LDAP://" & objSysInfo.ComputerName) 
objComputer.Put "managedBy", objSysInfo.Username 

Open in new window

Next, give authenticated users the write permission to a computer's description.

Then you can use this power shell script to query the logged on user. You will need the Quest AD Cmdlets.

Add-PSSnapin Quest.ActiveRoles.ADManagement

$User= read-host "What is the firstname,last name, or Username of the user?"
Get-QADComputer -ManagedBy (Get-QADUser $User) | Format-Table Name,Managedby

Author Comment

ID: 38850949
Thanks for your response. Do you mean to set this code to run when users log on to the network? If so, I don't have the permissions to make that work. I have no network or ActiveDirectory permissions beyond standard users.
I hoping to find code similar to this:
    Dim cnn As Object 'ADODB.Connection
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Provider = "ADsDSOObject"
    cnn.Open "Active Directory Provider"
    'Create the query
    Dim strSQL As String
    strSQL = "SELECT distinguishedName" & _
             " FROM 'LDAP://" & strDomain & "'" & _
             " WHERE objectCategory='user'" & _
                   " AND samAccountName = '" & strUsername & "'"
    'Get the data and return the result
    Dim rst As Object 'ADODB.Recordset
    Set rst = cnn.Execute(strSQL, , 1)
    If Not rst.EOF Then _
        GetActiveDirectoryFullName = Split(Mid(rst.Fields("distinguishedName"), Len("CN=") + 1), ",")(0)

Open in new window

... except it queries the distinguishedName from Active Directory by passing the username (and domain). But username is the missing information for me. I'm trying to find the logged on username based on the computer name.
LVL 22

Expert Comment

by:Joseph Moody
ID: 38850971
Are you an admin of the remote machines?
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

LVL 58
ID: 38850995
Give the code below a shot.  I had it sitting to try it out, but never got around to it.  Not sure if it works or not.  If it doesn't, let me know and I'll play with it.


Function getLDAPName(clockNumber As String)

     'Declare Variables
     Dim objAdoCon, objAdoCmd, objAdoRS
     Dim objUser, objRootDSE
     Dim strDomainDN, strUserFullName
     Dim intAnswer As Integer

    On Error GoTo Err_NoNetwork
     ' Get current logged in user name
     'strUserFullName = Environ("UserName")

     ' Get the DN of the user's domain
     Set objRootDSE = GetObject("LDAP://rootDSE")
     strDomainDN = objRootDSE.Get("defaultNamingContext")
     ' Search the domain for the user's account object
     Set objAdoCon = CreateObject("ADODB.Connection")

     objAdoCon.Open "Provider=ADsDSOObject;"
     Set objAdoCmd = CreateObject("ADODB.Command")
     Set objAdoCmd.ActiveConnection = objAdoCon

     objAdoCmd.CommandText = _       "SELECT ADsPath FROM 'LDAP://" & strDomainDN & "' WHERE " & _       "objectCategory='person' AND objectClass='user' AND " & _       "sAMAccountName='" & clockNumber & "'"          
     Set objAdoRS = objAdoCmd.Execute

     ' If found, get the displayName attribute.
     If (Not objAdoRS.EOF) Then
       Set objUser = GetObject(objAdoRS.Fields("ADsPath").Value)

       'Get common name
       objUser.GetInfoEx Array("CN"), 0
       commonName = objUser.Get("CN")

       'get first name
       objUser.GetInfoEx Array("givenName"), 0
       firstName = objUser.Get("givenName")

       'get last name
       objUser.GetInfoEx Array("SN"), 0
       lastName = objUser.Get("SN")

       'get display name
       objUser.GetInfoEx Array("DisplayName"), 0
       DisplayName = objUser.Get("DisplayName")

       getLDAPName = commonName
      ' handle "not found" error here
      GoTo Err_NoNetwork
     End If        

     On Error resume next

     Set objUser = Nothing

     Set objAdoRS = Nothing
     Set objAdoCmd = Nothing


     Set objAdoCon = Nothing

     Set objRootDSE = Nothing

     Set WshNetwork = Nothing

     Exit Function        

      getLDAPName = "Error"
      GoTo Exit_Sub

 End Function

Author Comment

ID: 38851091
@JMoody10, I don't have admin privileges on any PCs. Even if I log in locally, directly on the PC itself, using my login name assigned by our IT department, I only have standard, limited user permissions. Only IT technician users have privileges like admin. I'm only a database admin - it's a big company.

I tried the code. With MS Access Option Explicit settings I had to declare some variables. My best guess was:
    Dim WshNetwork As Object
    Dim commonName As Variant
    Dim firstName As Variant
    Dim lastName As Variant
    Dim DisplayName As Variant

For clockNumber, I passed the name of a PC. I tried once with someone elses PC and once with mine.
The result said "Error".
This was base on an empty recordset, apparently.
After executing the query from this SQL:      
objAdoCmd.CommandText = _
     "SELECT ADsPath FROM 'LDAP://" & strDomainDN & "' WHERE " & _
     "objectCategory='person' AND objectClass='user' AND " & _
     "sAMAccountName='" & clockNumber & "'"
On executing this line
If (Not objAdoRS.EOF) Then
... it executed:
      ' handle "not found" error here
      GoTo Err_NoNetwork
... so it apparently found no records from the SQL statement.

Maybe because I don't have the clockNumber parameter right? For clockNumber, I plugged in the computer name. (That's the only piece of data I have about who is using the file).
LVL 58
ID: 38851161
Would be the users signon:

sAMAccountName='" & clockNumber & "'

fot testing, change this line at the top:

'strUserFullName = Environ("UserName")


clockNumber = Envrion("UserName")

 If that works, then I'll adjust the code to use an API rather then a call to Envrion()  (which can be easily spoofed).


Author Comment

ID: 38851258

I think we're close. I'm trying to get the username from the computer name. I don't have the username. Environ("UserName") only works for the user that's logged on - I won't have that because I'm using the MS Access Lockfile (it holds the computer name but not the win/network logged on username).
In that SQL string, instead of using the SAMAccountName in the Where clause, is there a way to use the Computer Name and get the SAMAccountName from it?
The code might look something like this:
    objAdoCmd.CommandText = _
     "SELECT ADsPath FROM 'LDAP://" & strDomainDN & "' WHERE " & _
     "objectCategory='person' AND objectClass='user' AND " & _

Thanks for looking at this. I think we're getting close.
LVL 11

Expert Comment

ID: 38851262
The thing is that Active Directory contains data pertaining to the user -- their name, username, email address, and a variety of other data the focus on a person and the permissions they have on your network.  Plus, AD allows you to group those people into groups that share the same permissions.

Active Directory does not maintain the information as to which computer(s) you are logged on to.  With that in mind, querying Active Directory for the information you seek is not going to yeild the results you want.
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 38851342
<<I'm trying to get the username from the computer name. I don't have the username. Environ("UserName") only works for the user that's logged on - I won't have that because I'm using the MS Access Lockfile (it holds the computer name but not the win/network logged on username). >>

 That's a problem then.  I know your question stated that, but I assumed you would be able to get the user name.

 Windows does keep track of who is using a share (both user and computer name), so it should be possible to pull the list, but that's not going to be through AD.

 I'll see if I can't think of another way...


Author Comment

ID: 38851513
Okay. Thanks. I believe I understand. There's a user account, computer account and a group account in Active Directory. But correlating users to the computer they are logged on with is possibly a customization that Network administrators might implement? It's a dynamic, potentially constantly changing many-to-many look-up table where a user can be logged on to more than one PC and a PC can have more than one users.
This is, maybe, how something like the script Jmoody10 posted could be used. The script would have to be run from each PC whenever a user logs on. By default, it sounds like, Active Directory doesn't store this.
Typically, WMI Service can obtain this information, but something on our network is blocking it.
If anyone has any ideas how I can get the username by using the computer name without calling the WMI Service object, I'd appreciate the help.
LVL 11

Expert Comment

ID: 38851693
You can try to use the API call of NetWkstaUserEnum.  I used to use that before I switched to WMI.  I could not find an example of my previous usage, but after a Bing search on the API call, this particular link seemed decent ...

LVL 11

Expert Comment

ID: 38851712
Here is another sample from a previous EE thread ...


It may be a better sample since it is not based in a Class module.

That particular thread references another site as well that has tons of API calls for network information, including getting the remote user name ...


Author Comment

ID: 38851914
Thanks datAdrenaline,

On first effort (running previous EE thread), NetWkstaUserEnum returned a value of 5, which indicated an error. CopyMemory couldn't populate uinfo.

I will look at it some more, but not working yet.
LVL 35

Expert Comment

by:Nirmal Sharma
ID: 38854220
Or esiest way to use Dynamic SpotAction Tool.

Author Comment

ID: 38871355
Thanks for everybody's help. I'm still trying to solve this using everyone's responses. Might take a few more days before I can get through it. I'm using a temporary workaround until I can implement a better solution - I'm appending the username and PC name into a table when they open the database, then getting the PC name from the .laccdb lockfile and showing the username that was associated with it in my table.
LVL 58
ID: 38873547
Well if your willing to do that, just call the windows API for the User and computer name.

call WhoAmI(True) to get the username or WhoAmI(False) to get the station name.

Paste the below into a module.  Declares go at the top of the module.


Private Declare Function GetComputerNameA Lib "kernel32" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function GetUserNameA Lib "advapi32.dll" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function WhoAmI(bReturnUserName As Boolean) As String

        ' Function returns either user name or computer name

        Dim strName As String * 255

10      If bReturnUserName = True Then
20        GetUserNameA strName, Len(strName)
30      Else
40        GetComputerNameA strName, Len(strName)
50      End If

60      WhoAmI = left$(strName, InStr(strName, vbNullChar) - 1)

End Function
LVL 11

Accepted Solution

datAdrenaline earned 1000 total points
ID: 38876928
With the proliferation of 64bit installations, I personally avoid API calls if at all possible.  So to get the local user name and computer name I suggest using something like this following:

Public Function WhoAmI(bReturnUserName As Boolean) As String

    Dim strUserName As String
    'Dim strUserDomain As String
    Dim strComputerName As String
    'Set the properties
    With CreateObject("wscript.network")
        strUserName = .UserName
        'strUserDomain = .UserDomain
        strComputerName = .ComputerName
    End With
    WhoAmI = IIf(bReturnUserName, strUserName, strComputerName)
End Function

Open in new window


Author Closing Comment

ID: 38905582
Thanks for everybody's help. I was not able to find a way to get the username via computer name through Active Directory, WMI Service, or NetWkstaUserEnum. As noted by JDettman and DatAdrenaline, Active Directory does not contain this information (at least, not by default configuration). WMI Service appears to be configured to block access by non-admin users. NetWkstaUserEnum returned an error, not sure why (suspect it does not work on our current network/PC OS).

I had to use a work-around to solve this problem. I'm appending the username and PC name (using scripts posted by DatAdrenaline and JDettman) into a table when they open the database, then getting the PC name from the .laccdb lockfile and showing the username that was associated with it in my table.
LVL 11

Expert Comment

ID: 38905618
Thanks for the followup!  That technique you are employing is a common one.  So, while it may not be exactly what you want, it is a fairly reliable and widespread.

Good luck on your project!

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Group policies can be applied selectively to specific devices with the help of groups. Utilising this, it is possible to phase-in group policies, over a period of time, by randomly adding non-members user or computers at a set interval, to a group f…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Suggested Courses

609 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