locked
How to get Current Login User ID in MS Access 2010 ? RRS feed

  • Question

  • Hi,

    I am using MS Access 2010. While using MS Access 2003 we were used Environ('UserName') to get the Login UserID but this is not working in MS Access 2010 and also giving an error.

    Please let me know How to get the current login user ID in MS Access 2010 ?

    Thanks in Advance.

    Thursday, April 25, 2013 5:14 AM

All replies

  • Hi Kannancsit

    Works fine for me.

    Only difference: You have to write Environ("UserName") (" instead of ')
    Where doesn't it not work? In VBA, in an Expression or in a SQL Statement?

    To check, what the environmentvariable contains open a command prompt and in there enter:
    c:\> SET

    You should be able to access all these environment variables with the Environ() method from VBA and from expressions. Not 100% sure, but It does (and also in earlier version did) not work inside an SQL statement directly. To use it in an SQL Statement you can create a user define function, for example

    Public Function getUserName() AS String
     getUserName = Environ("USERNAME")
    End Function


    and then use

    SELECT getUserName() AS UserName, ...
      FROM ...

    Henry

    "Kannancsit" schrieb im Newsbeitrag news:04ecae50-c85d-49ef-823c-03e4576a99e1@communitybridge.codeplex.com...

    I am using MS Access 2010. While using MS Access 2003 we were used
    Environ('UserName') to get the Login UserID but this is not working in
    MS Access 2010 and also giving an error.


    Thursday, April 25, 2013 5:24 AM
  • CurrentUser method to return the name of the current user of the database.

    Application.CurrentUser
    Thursday, April 25, 2013 5:34 AM
  •  Thanks Henry,

     My Problem is , in Access 2003 table we used Environ("UserName") for one of the column like below that is working fine

      DefaultValue = Environ("UserName")

    But when i try the same in Access 2010 it is not working .

    Thanks

    Thursday, April 25, 2013 5:40 AM
  • You can also use API. see below code.

    Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    
    Function UserNameWindows() As String
       
        Dim lngLen As Long
        Dim strBuffer As String
       
        Const dhcMaxUserName = 255
       
        strBuffer = Space(dhcMaxUserName)
        lngLen = dhcMaxUserName
        If CBool(GetUserName(strBuffer, lngLen)) Then
            UserNameWindows = Left$(strBuffer, lngLen - 1)
        Else
            UserNameWindows = ""
        End If
    End Function
    
    Sub test()
    MsgBox UserNameWindows
    End Sub
    

    Thursday, April 25, 2013 5:42 AM
  • Hi Sontosh Vi

    This is not what he wants. This will return "admin" if the access user security isn't used, else the Access user, not the Windows UserName (login of Windows)

    Henry

    Thursday, April 25, 2013 6:09 AM
  • Hi Kannancsit

    This is a result of the new sandbox mode of the ACE, the new db engine behind Access.

    Have a look at this article here, how to change the Sandbox Mode with registry settings:

    ACE sandbox mode

    If you set it to 2 or 0 (default is 3) the default value can be set as you did in A2003.

    The SandBoxMode value itself is (not as mentioned in the article) in:

    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines

    Hint: It's not a very good practise to use default values in the table itself. The user should never enter data into the table itself (see here: 10 Commandments for Access). You should use forms instead and in there you can either set the value in the Before Insert event or in the Before Update event. Maybe it's time now to change your application to not be forced to change the registry setting on all the machines where your application is running on.

    HTH

    Henry


    Thursday, April 25, 2013 6:22 AM
  • This one will give you the name and not admin

    Option Compare Database
    Option Explicit
    Declare Function GetUserNameA Lib "advapi32.dll" (ByVal IpBuffer As String, nSize As Long) As Long
    '
    
    Function GetUserName(Optional fAppendAccessUserName As Boolean = False, _
                Optional lngMaxLen& = 255) As String
      Dim lngLen&, strBuf$
      If lngMaxLen > 255 Then lngMaxLen = 255
      strBuf = Space(lngMaxLen)
      If CBool(GetUserNameA(strBuf, lngMaxLen)) Then
        GetUserName = Left$(strBuf, lngMaxLen - 1)
      Else
        GetUserName = ""
      End If
      If fAppendAccessUserName Then
        If GetUserName <> "" Then GetUserName = GetUserName & " "
        GetUserName = GetUserName & "(" & CurrentUser & ")"
      End If
     End Function
    


    Chris Ward

    Thursday, April 25, 2013 6:26 AM
  • It's not a very good practise to use default values in the table itself. The user should never enter data into the table itself

    Henry

    I don't think this is what the OP is talking about. I think the OP is saying the field in the record is populated just before the record is committed using the BeforeUpdate Event of the Form to Call the Function example with the code above make the call from the Form like

    Private Sub Form_BeforeUpdate(Cancel As Integer)
            If Me.NewRecord = True Then
                Me!EnteredOn = Now
                Me!EnteredBy = GetUserName
            Else
                Me!UpdatedOn = Now
                Me!UpdatedBy = GetUserName
            End If
    End Sub


    Chris Ward

    Thursday, April 25, 2013 6:35 AM
  • Hi Santoish Vi

    This will not help either. You can't call user defined functions for default field values in tables.

    Henry


    Thursday, April 25, 2013 6:36 AM
  • Hi Santoish Vi

    This will not help either. You can't call user defined functions for default values of tables.

    Henry

    Henry the OP does not appear to be asking about a default value.

    Edit 20130425 1:42 AM

    My mistake Henry I see the later post now. This brings to mind the use of Table Triggers which is introduced in A2010. Can those be used to call a Function or be used to set a default value in a record?


    Chris Ward


    • Edited by KCDW Thursday, April 25, 2013 6:43 AM
    Thursday, April 25, 2013 6:38 AM
  • Hi Chris

    Pls read the answer of the OP to my question in the 1st posting. He wrote:

    My Problem is , in Access 2003 table we used Environ("UserName") for
    one of the column like below that is working fine

     DefaultValue = Environ("UserName")

    So it's exactly this, what he wants...

    Henry

    "KCDW" schrieb im Newsbeitrag news:f139b43c-6bf3-4bcf-8740-70bc79462428@communitybridge.codeplex.com...

    It's not a very good practise to use default values in the table
    itself. The user should never enter data into the table itself

    I don't think this is what the OP is talking about. I think the OP is
    saying the field in the record is populated just before the record is
    committed using the BeforeUpdate Event of the Form to Call the Function
    example with the code above make the call from the Form like

    Thursday, April 25, 2013 6:45 AM
  • Good Morning again Henry,

    We seem to be missing each other pls read my addendum above.


    Chris Ward

    Thursday, April 25, 2013 7:02 AM
  • Santhosh

    I cannot able to use Application.CurrentUser as Default value

    It's giving an error "Could not find Application.CurrentUser'

    Henry

    I checked the Sqandbox mode but unable to find the key in the registry.

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\SandBoxMode

    Access Connectivity Engine is not available under 12.0

    Please

    Thanks everyone for Help.....

    Thursday, April 25, 2013 9:07 AM
  • He is not talking about fields and recordsets, but about tables and columns. So my interpretation is different. If he would use fields (or controls in forms) he wouldn't have any problems in A2010 compared to A2003 with using Environ("USERNAME"), but the in A2007 introduced ACE Sandbox mode don't allow this one anymore. He is asking why this doesn't work in A2010 anymore and Envirion("USERNAME") in VBA isn't blocked by VBA Sandbox mode, it's blocked by the ACE Engine Sandbox mode. I really tested it before I posted my resolution, because I for myself wanted to know what could be the difference. Please try it with setting the Defaultvalue in a table field (in design of the table) to "=Environ("USERNAME")". It will not work unless you set the ACE Engine Sandbox mode to 0 or 2. Default is 3.

    Henry

    Thursday, April 25, 2013 9:09 AM
  • Hi Santhosh

    As I wrote in my posting: The correct key is

    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines

    The linked artikle is for A2007 and probably for a 32-bit OS. The here mentioned registry path is for Windows 7 64-bit with Office 2010 32-bit installed on it, what mostly is the case.

    If you can't find it here or have another kind of installation, please search the registry  by pressing Ctrl+F for "Access Connectivity Engine". There are a few entries inside but only one has the ..\Engine\SandBoxMode value inside.

    HTH

    Henry

    Thursday, April 25, 2013 9:15 AM
  • One thing which I don't think has been mentioned here is that it is very easy for a moderately knowledgeable user to change an environmental variable's value, and masquerade as another user.  If this is a possible issue then, rather than calling the Environ function, call the Windows API GetUserName function as detailed in earlier posts here.  You'll find examples of its use in ChangedRecordDemo.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Ken Sheridan, Stafford, England

    Thursday, April 25, 2013 11:32 AM
  • That was correct in Win98 times. You could open a command prompt, change the environment variable and then start Access 97. So you could manipulate it.

    Since WinXP this doesn'r work anymore. Access gets it's own environment and don't inherit it from the command prompt. I tried it a few years ago and didn't don't a way to do it or at least not a way that would be easy.

    On the other hand, Environ() is built in Jet/ACE so you can use it also there where UDFs aren't allowed such as the here mentioned Defaultvalues for columns in tables.

    In addition even Jet won't read the same Environ value as VBA, ess it has it's own implementation.

    Anyway I'd prefer using forms and the mentioned API call if I can assure records are only added by Forms but not directly to tables or by other applications.

    Henry

    Thursday, April 25, 2013 11:49 AM
  • Since WinXP this doesn'r work anymore. Access gets it's own environment and don't inherit it from the command prompt.

    Henry

    Sorry Henry, I just did it in Win7 Ultimate, Win7 Enterprise & Windows 8 Desktop Mode with A2007 and A2013.

    Here is a quote from Douglas J Steele MVP from this link

    Environ is extremely easy to spoof!

    Try the following:

    1. Open a command prompt
    2. Enter the following command in that command prompt window: SET UserName=xxx
    3. Without closing the command prompt window, launch msaccess.exe
    4. Environ$("username") will return xxx

    Chris Ward

    Thursday, April 25, 2013 1:18 PM
  • Environ is extremely easy to spoof!
    I do not agree with the word "extremely". Especially, taking into account that command prompt is almost always disabled in a corporate environment. Of course, it's easy to create a .bat or .vbs file somewhere in a network shared folder, but it's not extremely easy for the most part of users. :) Just my 2c.

    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru


    Thursday, April 25, 2013 1:54 PM
  • Yes, Doug is right. It's still working, even used in the defaultvalue property of a table field. Just had to give it a try again.

    You can even create a cmd file to start access where you set the environment variable before starting Access like this:


    SET username=yyy
    "C:\Program Files (x86)\Microsoft Office\Office14\msaccess.exe"

    So using Environ() is a not so good habit if you have to be sure you get the correct username.

    Anyway: I can't find another way to get the username for the fields defaultproperty in tabledefs. One can't call UDFs from there.

    Henry

    Friday, April 26, 2013 2:45 AM
  • One thing which I don't think has been mentioned here is that it is very easy for a moderately knowledgeable user to change an environmental variable's value, and masquerade as another user. 


    Ken Sheridan, Stafford, England

    What would he purpose of changing an environment variable be? Would changing the username elevate user priveledges somehow? Or would it just obsfuscate the user. Can you go into more detail about this? I am currently researching policy for sandboxing in our environment, and I need some concrete evidence as to why wrapping the unsafe expressions in VBA code or API is better than a plain old Environ function call. Is there a way that spoof of a username could elevate priveledges in the database? Also what about checking the logged in username against AD or something along those lines, is that a feesible solution? Thank you.  
    Monday, July 29, 2013 3:22 PM
  • What would he purpose of changing an environment variable be?
    It depends entirely on the context, and the potentially malicious intent of the user.  In an application where the current user's login name is being used as the basis for row-level security for instance, if the environmental variable is used for this, provided I know someone else's user name, I could log into the system as normal, but open the database as that user with ludicrous simplicity, and then have access to rows which contain data confidential to that user.  Where the Windows API GetUsername function is used I would have to login into the system as that user, which would require me to also know their password.

    Ken Sheridan, Stafford, England

    Monday, July 29, 2013 5:07 PM
  • Thanks for the explanation. So am I understanding correctly that the Windows API returns the logged in user, but is there a way to verify the logged in user against active directory? In other words, the API is getting the username from the system, not AD, correct? But is there a way to validate against AD? 

    I would think that since the user is logged in, that he is already authenticated and thus the username would be valid. 

    I guess what I am looking for is if there is a way to have a log in screen open when an Access database loads, that asks for a username and password, and then verifies that password against the AD. 

    • Edited by cyber-frog Tuesday, July 30, 2013 1:28 PM clarification of need
    Tuesday, July 30, 2013 12:57 PM
  • I'm not quite sure exactly what you have in mind, but for the use of Active Directory to provide security in MS Access the blog by Tom van Stiphout et al at:

    http://www.accesssecurityblog.com/post/2011/02/05/Securing-Access-databases-using-Active-Directory.aspx

    may be what you are looking for.

    Ken Sheridan, Stafford, England

    Tuesday, July 30, 2013 4:46 PM
  • If your user is logged on and your workstations don't allow to logon locally the user must be authenticated in the Active Directory.

    see also following link here:

    VBA: Login using Windows Authentication

    If you want to retrieve more information than only the username to ensure if the user is logged on to a domain or locally or if the domain is your domain you may use the IADsADSystemInfo utility.

    You may use following function based on the mentioned utility to retrieve the AD Domain the user is logged in to:

    Public Function getDomain() As String
      Dim objAdSys As Object
      Set objAdSys = CreateObject("ADSystemInfo")
      getDomain = objAdSys.DomainShortName
      Set objAdSys = Nothing
    End Function

    HTH

    Henry

    Wednesday, July 31, 2013 2:13 AM
  • Thanks, I will take a look at that.
    Wednesday, July 31, 2013 5:21 PM
  • Thanks Henry for the information.
    Wednesday, July 31, 2013 5:21 PM
  • Chris,

    Can you tell me how to get this into A2010?  I can't seem to get a control to read it.

    Thursday, January 2, 2014 2:37 PM
  • Chris,

    Can you tell me how to get this into A2010?  I can't seem to get a control to read it.

    Just back from vacation!!! Now I can relax:)

    1. The code block should be entered into a standard module
    2. you will need to have the fields desired contained in the Table the Form is based
    3. Add the fields to the Form is they are not already there
    4. Then add to the Fields on the Form a calling code to the function

    Since I cannot see your app then generally you could use the following as the Table Field Names "EnteredBy" and "EnteredTime"

    Use the Add Fields pane to add the fields to the Form

    Use code similar to this in the Forms Before Update Event

    Private Sub Form_BeforeUpdate(Cancel As Integer)
            If Me.NewRecord = True Then
                Me!EnteredTime = Now
                Me!EnteredBy = GetUserName
    'You can add in the following fields also to keep the most recent change listed. Just add the following fields to the Table as before and it should work for you. Let me know if you still have problems
    '
            Else
                Me!UpdatedTime = Now
                Me!UpdatedBy = GetUserName
            End If
    End Sub
    

    Hth


    Chris Ward

    Monday, January 6, 2014 8:14 PM
  • Here is what I've used in a split-database Access 2010 accdb for about 3 years without a problem. What I've noticed is all the responses below left out the $ after environ. 

    Dim sUserName as String

    sUserName=(Environ$("Username"))


    • Edited by LatteLea Saturday, October 24, 2015 3:36 AM
    Saturday, October 24, 2015 3:36 AM
  • You do realize that this is an old thread?  The use of the Environ function was discussed in the thread and dangers of doing so pointed out.  The value of an environmental variable is easily changed, so a malicious user with a moderate degree of confidence could masquerade as another user.  

    The $ character is the type declaration character for a string data type.  It is optional in this context.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Saturday, October 24, 2015 2:47 PM Typo corrected.
    Saturday, October 24, 2015 2:46 PM