apiGetUserName Lib "advapi32.dll"

M

Me

I have a function apiGetUserName that uses - Lib "advapi32.dll"
to get me the username.

But this gets me the windows username with which I am logged on to the
computer.

I want to get the username with which I am logged on to SQL server database.
Please note its not the access database login, but sql server login.

Thank you very much in advance,
-Me
 
D

Douglas J. Steele

You need to get that from SQL Server, not from WIndows.

You can create a pass-through query that retrieves the name from SQL Server.
Unfortunately, I can never remember which keyword you need to use: it's one
of USER_NAME, CURRENT_USER, SESSION_USER or SYSTEM_USER.

Go into SQL Server and run the following:

SELECT USER_NAME(), CURRENT_USER, SESSION_USER, SYSTEM_USER

Which of those 4 is what you're looking for?
 
M

Me

Doug,

I am looking for current user, will try it out and let you know if it works
for
what I plan to do.

Thanks much,
-Me
 
M

Me

Douglas,

Here is what I tried -
When I run the following query in 'Query Analyzer', I get the desired result -
select user, current_user, session_user,system_user


However, when I run the same in Acces Database I get error since it doesn't
recognize from where its supposed to retrieve the data.


So I linked the sysusers table and modified the query in Access as follows -

SELECT name, *
FROM sysusers
WHERE name=session_user;

It doesn't recognize session_user and it asks for session_user.

Do you have other suggestions?

Thank you,
-Me
 
T

Terry Kreft

I've just tried what Doug suggested and it worked fine.

I suspect you missed the phrase "pass-through".

You create a pass-through query so that the SQL is executed on the SQL
Server not by Access.
 
M

Me

Hi Terry,

Thanks for the reply, you are right, once I created it as pass-thru' query,
it worked.
But the problem is every time I run this query, it brings up the to ODBC
datasource settings to link with the DATA SOURCE entry and then asks me for
userid/pwd.
Is there any way to avoid it?

Upon launch of this access database, users enter their userid/pwd. Then
I expect the pass-thru' query we discussed should give me the username
instead of asking again.

Thank you,
-Me
 
T

Terry Kreft

If you include those in your ODBC string then it shouldn't prompt you the
relevant keys are UID= and PWD= .

To do this with the user name and password your users enter at login you
would need to capture those details and then alter the connect string
property of the query.
 
Top