VBA – Recognize User, Get Username

It can be very useful to be able to automatically recognize the current database user. This could be to:

automate the login process
log which user add/edited/deleted records
etc.

Today, I thought I’d quickly cover a couple way this can be accomplished:

Environ Function
WScript Objects
WMI
API
PowerShell

Environ

One of the easiest way to quickly return the currently logged in user’s Windows username is to simply check the computer’s username environment variable by using the Environ function.

Environ("username")

The issue with technique is that there are relatively easy ways to spoof the environment variable thus enabling a user to impersonate any user they choose. So I don’t recommend this approach.

WScript

The second approach is to use WScript to get the Windows username. Now, with Wscript there are a couple approaches that can be used.

WScript Shell Object

The first, just like the Environ function approach, reads the environment variable by utilizing the Wscript.Shell ExpandEnvironmentString Method and thus is also not a reliable solution.

CreateObject("WScript.Shell").ExpandEnvironmentStrings("%USERNAME%")

WScript Network Object

The better approach is to use the WScript.Network object. Unlike environment variables, this can not easily be spoofed. The code to do so would look like:

CreateObject("WScript.Network").UserName

WMI

Another approach, more convoluted however, is to use WMI to list the active sessions and then cross-reference the session with the logged in username. It can be done by doing something along the lines of:

'---------------------------------------------------------------------------------------
' Procedure : WMI_GetUsernames
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return a dictionary (unique values) of usernames for the specified computer
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sHost     : host computer to query, omit for the local PC
'
' Usage:
' ~~~~~~
' See the TestMe() proc below
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2018-04-13              Initial Release
' 2         2018-04-26              Code cleanup for Option Explicit, sorry!
'---------------------------------------------------------------------------------------
Public Function WMI_GetUsernames(Optional sHost As String = ".") As Object    'Scripting Dictionary
    'Ref: https://msdn.microsoft.com/en-us/library/aa394189(v=vs.85).aspx
    '     https://msdn.microsoft.com/en-us/library/aa394172(v=vs.85).aspx
    '     https://msdn.microsoft.com/en-us/library/aa384793(v=vs.85).aspx
    On Error GoTo Error_Handler
    Dim oWMI                  As Object    'WMI object to query about the PC's OS
    Dim sWMIQuery             As String    'WMI Query
    Dim oLogonSessions        As Object
    Dim oLogonSession         As Object
    Dim oUsers                As Object
    Dim oUser                 As Object
    Dim dictUsers             As Object    'Scripting Dictionary
 
    Set WMI_GetUsernames = Nothing    'Always empty it!
    Set dictUsers = CreateObject("Scripting.Dictionary")    'New dictionary
    Set oWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & sHost & "\root\cimv2")
    sWMIQuery = "SELECT * " & _
                "FROM Win32_LogonSession " & _
                "WHERE LogonType=2"
    Set oLogonSessions = oWMI.ExecQuery(sWMIQuery)
    For Each oLogonSession In oLogonSessions
        'Now that we have active sessions, let find out who is running them
        sWMIQuery = "Associators of " & _
                    "{Win32_LogonSession.LogonId=" & oLogonSession.LogonId & "} " & _
                    "WHERE AssocClass=Win32_LoggedOnUser Role=Dependent"
        Set oUsers = oWMI.ExecQuery(sWMIQuery)
        For Each oUser In oUsers
            With oUser
                '                Debug.Print .Name
                If dictUsers.Exists(.Name) = False Then dictUsers.Add .Name, .Name
            End With
        Next
    Next
    Set WMI_GetUsernames = dictUsers
 
Error_Handler_Exit:
    On Error Resume Next
    Set dictUsers = Nothing
    Set oUsers = Nothing
    Set oUser = Nothing
    Set oLogonSession = Nothing
    Set oLogonSessions = Nothing
    Set oWMI = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: WMI_GetUsernames" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

and it can be used by doing something like:

Sub TestMe()
    Dim User As Variant
 
    For Each User In WMI_GetUsernames.keys
        Debug.Print User
    Next
End Sub

API

Another, and better solution IMHO, is to use an API. Below is a very well known API that was written by Dev Ashish that I tweaked to make it compatible with x32 and x64 usage.

#If VBA7 And Win64 Then
    'x64 Declarations
    Private Declare PtrSafe Function apiGetUserName Lib "advapi32.dll" Alias _
            "GetUserNameA" (ByVal lpBuffer As String, _
                            nSize As Long) As Long
#Else
    'x32 Declaration
    Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
            "GetUserNameA" (ByVal lpBuffer As String, _
                            nSize As Long) As Long
#End If
'******************** Code Start **************************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
' Adapted for x64 usage from: http://www.theaccessweb.com/api/api0008.htm
'
Function fOSUserName() As String
' Returns the network login name
    Dim lngLen As Long, lngX  As Long
    Dim strUserName           As String
    
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = vbNullString
    End If
End Function
'******************** Code End **************************

PowerShell

If you want, you can even use PowerShell to get the username of the current user. If you implement my PowerShell functions from:

VBA - Running PowerShell Commands

Have you ever wanted to run a PowerShell command from within VBA and get back a value?

Then you can simply retrieve the username using a single line like:

Debug.Print PS_GetOutput("[System.Environment]::UserName") 'Send the username to the immediate window

If ever you want to get the domain/username it is also very easy to achieve by simply doing:

Debug.Print PS_GetOutput("[System.Security.Principal.WindowsIdentity]::GetCurrent().Name") 'Send the username to the immediate window

I’ve also seen people recommend using:

Debug.Print PS_GetOutput("$env:UserName") 'Send the username to the immediate window

but once again, be careful here as this is reliant on the environment variable which can be spoofed!

Conclusion

As you can see, there are a multitude of ways to retrieve the current user’s username, but not all are reliable/secure.

My only recommendation is to avoid anything that is based on the environment variable as that can be spoofed very easily allowing people to impersonate anyone they want. Beyond that, the choice is yours!

Also, some will state that APIs are the efficient way to do things. I say Bah Humbug to them. When I tested, the difference was measured in microseconds, so negligible and when I look at the simplicity of the CreateObject(“WScript.Network”), 1 line, no API declarations to worry about or adapt for bitness… Add to that the simple fact that you don’t call this type of function over and over, it’s a one time deal.

Whatever the approach you select, a great way to implement this is to simply call it once at the startup of your application, set a global variable or TempVars and use the variable/tempvar thereafter throughout your code.

Page History

Date	Summary of Changes
2018-04-12	Initial Release
2022-09-07	Added the PowerShell section Added the Conclusion section

17 responses on “VBA – Recognize User, Get Username”

Dmitriy Sonnikh April 26, 2018 at 1:53 am

Error in text (WMI)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Set WMI_GetUsernames= Nothing ‘ skipped s in WMI_GetUsernames
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
skipped
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim oUsers As Object
Dim oUser As Object
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
and
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Set oUser = Nothing
1. Daniel Pineault Post authorApril 26, 2018 at 9:42 am
  
  Thank you. The code has been tweaked and is Option Explicit compliant now.
Dmitriy Sonnikh April 26, 2018 at 2:38 am

Public Function LoginName() As String
‘ Full Name in System

Dim oUsers As Object ‘IADsUser
Dim strUserName As String
Dim strUserDomain As String
Dim FName() As String

strUserName = Environ(“username”)
strUserDomain = Environ(“USERDOMAIN”)
Set oUsers = GetObject(“WinNT://” & strUserDomain & “/” & strUserName & “,user”)
LoginName = Trim(oUsers.FullName)
Set oUsers = Nothing

‘Debug.Print LoginName

End Function
1. Daniel Pineault Post authorApril 26, 2018 at 9:41 am
  
  I wouldn’t use such code as it relies on Envrion() variables which are unsecure. You need to switch to WMI or APIs when getting the Username or Domain.
  1. Dmitriy Sonnikh April 27, 2018 at 6:02 am
    
    Function GetUserFullName() As String
    Dim WSHnet As Object
    Dim objUser As Object
    Dim UserName As String
    Dim UserDomain As String
    Dim UserFullName As String
    
    Set WSHnet = CreateObject(“WScript.Network”)
    UserName = WSHnet.UserName
    UserDomain = WSHnet.UserDomain
    Set objUser = GetObject(“WinNT://” & UserDomain & “/” & UserName & “,user”)
    GetUserFullName = objUser.FullName
    Set objUser = Nothing
    Set WSHnet = Nothing
    
    Debug.Print “User Full Name: ” & vbCrLf & GetUserFullName
    End Function
Dmitriy Sonnikh April 27, 2018 at 5:45 am

User for Net

Declare Function intWNetGetUser Lib “mpr.dll” Alias “WNetGetUserA” (ByVal _
lpname As String, ByVal lpUserName As String, lpnLength As Long) As Long

Public Function WNetGetUser(lpLocalName$) As String
Dim res&
Dim tbuf As String
Dim BufferSize&
Dim lenName As Integer

tbuf = String$(256, 0)
BufferSize = Len(tbuf)
res = intWNetGetUser(lpLocalName, tbuf, BufferSize)

‘Null
lenName = InStr(tbuf, Chr(0)) – 1
tbuf = Left(tbuf, lenName)
WNetGetUser = tbuf

End Function
Ben Sacherich May 9, 2018 at 10:16 am

This is a good run down of various ways to get the users Windows login name, but often that name is cryptic (numeric or abbreviated). A user will recognize their own name but not other peoples names. Like Dmitriy Sonnikh, I wanted a way to get the friendly display name that is stored in Active Directory. I like to store this name in each records Modified_By field when a record is changed. This makes it easy for any user to identify the person who made the last change.

Below is the function that I use:

‘**************************************************************
Public Function fGetDisplayName(Optional UserName As Variant) As String
‘ This will return the Active Directory Display Name for the passed NT login name or the current user.
‘ I modified the code to allow a passed parameter and added an error handler. BS 9/16/2013

On Error GoTo ErrorHandler

Dim WSHnet As Object
Dim UserDomain
Dim objUser As Object
Dim UserFullName

DoCmd.Echo True, “Retrieving user name…”
DoEvents ‘ in hopes of letting the screen redraw.

Set WSHnet = CreateObject(“WScript.Network”)
If IsMissing(UserName) Then
‘ No name passed. Use the current user name.
UserName = WSHnet.UserName
ElseIf IsNull(UserName) Then
fGetDisplayName = “”
Exit Function
End If

UserDomain = WSHnet.UserDomain
Set objUser = GetObject(“WinNT://” & UserDomain & “/” & UserName & “,user”)
UserFullName = objUser.FullName

‘ Debug.Print UserName, UserDomain, UserFullName

fGetDisplayName = UserFullName

Exit Function

ErrorHandler:
If err.Number = -2147022675 Then ‘ The user name could not be found.
‘ Return the NT user name or the passed user name.
fGetDisplayName = UserName
ElseIf err.Number = 462 Then ‘ The remote server machine does not exist or is unavailable.
‘ This happened when I was at a client site connected to the VPN. I don’t understand why.
fGetDisplayName = UserName
ElseIf err.Number = -2147463168 And Len(UserName) > 0 Then ‘ Automation error
‘ This can happen if a Display Name is passed to this function. Return the passed name.
fGetDisplayName = UserName
ElseIf err.Number = -2147024843 And Len(UserName) > 0 Then ‘ Automation error – The network path was not found.
‘ This can happen when working offline (not connected to the domain).
fGetDisplayName = UserName
Else
MsgBox “Error #” & err.Number & ” – ” & err.Description & vbCrLf & “in Function fGetDisplayName()”
fGetDisplayName = UserName
End If

End Function
‘**************************************************************

A few notes:
– This routine can be slow, especially if you are not connected to the domain.
– If this routine fails, it returns the Windows login name as a fallback.
– You can pass in a Windows login name for another user and it will return their display name.
– Because this routine can be slow, I use another function as my main way to get the display name, so this only needs to be called once. See below.

Public Function UserName() ‘ (Name this function whatever you are comfortable with.)

Static strDisplayName As String ‘ Use a static variable so the slow fGetDisplayName function doesn’t run more than once.

If strDisplayName = “” Then
strDisplayName = fGetDisplayName
strDisplayName = Replace(strDisplayName, “‘”, “”) ‘ Remove apostrophes from names like “O’Brien”
End If

UserName = strDisplayName

End Function
1. Daniel Pineault Post authorMay 9, 2018 at 1:36 pm
  
  That a nice addition to the username code.
  
  What I do is I have a table that houses the users Username and First/Last names, then I can cross-reference them as required but need only the UserName function to identify my users.
Justin Short May 14, 2018 at 4:29 pm

I would like to find code that explicitly returns the active/current/logged-in user of my db from anywhere in the world from anyone’s computer completely independent of the operating systems username. (i.e. I am logged into the db on Suzys computer all while while Suzy is logged into her computer. My name returns whenever called. Can you help me… please… ive looked everywhere and everyone says “just grab it from your OS.”

Thanks,
Justin
1. Daniel Pineault Post authorMay 15, 2018 at 8:41 am
  
  Take a look at http://www.devhut.net/2013/03/19/ms-access-who-is-logged-in/, I believe this is what you are looking for.
Tim D August 29, 2018 at 1:38 pm

Hi, I’ve been using the API version for years and suddenly it is randomly erroring out with a Type Mismatch error on Access 2016. I’ve updated the code to add the PtrSafe and checked the declarations to make sure all variables are assigned the same datatypes in the GetUserName and USERNAME functions. Here is my code:

General Declarations Section:
Public Declare PtrSafe Function GetUsername Lib “advapi32.dll” Alias “GetUserNameA” (ByVal lpBuffer As String, nSize As LongPtr) As LongPtr

Called Function:

Function USERNAME() As String
‘ Returns the name of the logged-in user
‘ Requires mdlAPI

Dim Buffer As String * 100
Dim BuffLen As LongPtr
BuffLen = 100
GetUsername Buffer, BuffLen
USERNAME = Left(Buffer, BuffLen – 1)

End Function

Am I missing something obvious?
1. Daniel Pineault Post authorAugust 29, 2018 at 4:58 pm
  
  Before anything else, PtrSafe, why? Are you running 32 or 64 bit version of Access? PtrSafe is only for 64 bit instances.
Dan M May 10, 2019 at 7:53 am

Why not use application.username?
1. Daniel Pineault Post authorMay 10, 2019 at 8:01 am
  
  Because the command is not available in all Office applications (for instance it isn’t available in Access or PowerPoint).
Brandon August 17, 2021 at 4:34 pm

Hi! I am trying to create a database that keeps up with users’ productivity on a secured network. I want the field to automatically capture their network ID when they open a new form. None of these Associates have any knowledge of Access, VBA, etc., so they will not be able to spoof. I am looking for an easy way to do this and was hoping to utilize Environ, but it doesn’t seem to be working no matter what I do. Do you have any suggestions? I have tried the following so far: Opened VBA Editor, inserted new module, and entered the following VBA:

Public Function GetUsername() As String
GetUsername = Environ(“Username”)
End Function

I navigated to the form, clicked on the corresponding field, and typed GetUsername() in the Default Value field of the Data tab.

Nothing happens. The field stays blank. I have also tried using GetUsername = Environ$(“Username”) as well. What am I missing? Thank you for your assistance!
j2associates March 10, 2022 at 5:30 am

Hello,
Thanks for the very helpful information. Is there an equivalent that returns the Domain Name? Thanks…
1. Daniel Pineault Post authorMarch 10, 2022 at 5:44 am
  How about trying something like:
```
CreateObject("WScript.Network").UserDomain
```