I have a MS Access front end with tables linked to SQL Server. I used a file DSN to link tables, and upon opening Access database the user has to enter SQL server userID and password.
Is there a way to extract that userID (not the password) from the established ODBC connection, for example to use it for display and audit purposes?
Yes, you can create a Pass-Through query in Access with just the statement...
SELECT CURRENT_USER
...then in the Property Sheet for that query click the ellipsis button beside ODBC Connect Str and select your File DSN. (When asked if you want to save the password in the connection string, say "No".)
Then save the Pass-Through query (I called mine "getCurrentSqlUser") and run it to get the name of the current user on the SQL server.
(Note: While testing this I logged in as sa and found that the query returned dbo. I think that's because I was logged in as a member of the sysadmin Server Role.)
