Environ("Username")

[jez123456]

Hi Experts

I have an access adp project connected to a sql server backend database. I
have an audit table which saves details of which user modified records on the
other tables.

We recently had to switch the connection properties from using NT integrated
security to using a specific username (GENLogin) and password.

When using NT integrated security, I recorded the user performing the table
updates using the sql server suser_sname() function, however, now that we use
the GENLogin for all users it’s GENLogin that gets saved in the audit table.
I need to be able to save which (windows) individual user has made the
changes.

Elsewhere in the adp code I use Environ("Username") to obtain the windows
user. Is there a way to enter this value in the audit table using a user
defined function?

Thanks

 

[Tony Toews [MVP]]

Elsewhere in the adp code I use Environ("Username") to obtain the windows
user. Is there a way to enter this value in the audit table using a user
defined function?

The problem with using the Environ variables is that the users can
change that value. The risk appears to only happen if you
open the command prompt, change the environment variable and then
execute the msaccess.exe from that command prompt. A daunting task
for 99.99% of the users

Changing the environment variable in the command prompt does not
affect other programs that you start from Windows. But only from
that particular command prompt.

However that 00.01% of the users that know about this could also be
the malicious users who want to blame data problems on other people.

Hmmm, being the sneaky bugger that I am I'd put a check in to compare
the value of the environment variable against the API call. If
different without an exceedingly good reason that'd be grounds to
terminate the employee.

Therefore we prefer using the code at
API: Get Login name
http://www.mvps.org/access/api/api0008.htm

Tony

 

[jez123456]

Hi Tony, thanks for the reply. I added the fOSUserName() function to a module
and this works ok when I call it from code behind a form.

Unfortunately, when I add this to the Default Value property of my UserName
column in the Audit table, I get the message

Error validating the default for column 'UserName'.
Do you want to edit the default?

When I click the ‘Yes’ button, the Default Value reverts back to
suser_sname() which was in there previously.

I’m wondering that the sql server Audit table will not be able to ‘see’ the
fOSUserName() function. Would I have to create this as a sql server user
defined function and if so how?

Thanks again for your time and help.