Query ActiveDirect for username from computer name

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 
objComputer.SetInfo

Select allOpen 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
pause

Jmoody10,
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)

Select allOpen 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.

Are you an admin of the remote machines?

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.

Jim.


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
     Else
      ' handle "not found" error here
      GoTo Err_NoNetwork
     End If        


Exit_Sub:
     On Error resume next

     Set objUser = Nothing

     Set objAdoRS = Nothing
 
     Set objAdoCmd = Nothing

     objAdoCon.Close

     Set objAdoCon = Nothing

     Set objRootDSE = Nothing

     Set WshNetwork = Nothing

     Exit Function        

Err_NoNetwork:
      getLDAPName = "Error"
      GoTo Exit_Sub

 End Function

@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.

@JDettman,
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:      
On executing this line
 
... it executed:
... 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).

Would be the users signon:

sAMAccountName='" & clockNumber & "'

fot testing, change this line at the top:

'strUserFullName = Environ("UserName")

to

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).

Jim.

Jim,

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 " & _
     "COMPUTER_NAME_FIELD='" & THE_COMPUTER_NAME_PASSED_AS_PARAMETER & "'"
Thanks for looking at this. I think we're getting close.

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.

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.

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 ...

http://www.andreavb.com/tip060006.html

Here is another sample from a previous EE thread ...

https://www.experts-exchange.com/questions/20923841/Identify-current-user-on-a-remote-Win2k-computer.html?anchorAnswerId=10658333#a10658333

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 ...

http://vbnet.mvps.org/index.html?code/network

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.

Or esiest way to use Dynamic SpotAction Tool.

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.

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.

Jim.

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

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.

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!