Getting the NT login ID in an Access form

Topic

New Reply

I would like to use the NT login ID in a parameter I am sending to SQL Server via ODBC Direct to execute a stored procedure. I know I can ask the user to enter it again but if I can use the authenticated user ID from NT, security obviously goes up several notches.

My goal is be able to check for it when a submit button is cliked on the form which asks for the stored procedure to execute and return a record set to Access.

I belive I am going to have to call a win api to get this done and which will all be new to me so any and all help as to how this would be done would be greatly appreciated.

Reply | Quote

Replies

I’m glad you asked this because I have a similar question. I don’t need the authentication just the NT User name would do me. Waiting with baited breath for the flood of answers.

Reply | Quote

I posted in the VB/VBA form and got this answer from Andrew Cronnolly, it seems to work in 98 haven’t tried in any NT versions yet, hope it helps.

Public Declare Function GetUserName Lib “advapi32.dll” _
Alias “GetUserNameA” (ByVal lpBuffer As String, nSize As Long) As Long

Function GetUser() As String
Dim lpBuff As String * 255
Dim ret As Long, UserName As String
ret = GetUserName(lpBuff, 255)
GetUser = Left(lpBuff, InStr(lpBuff, Chr(0)) – 1)
End Function

Reply | Quote

Magic. Thank you. I will still be interested in seeing the solution for the NT login ID.

Reply | Quote

Generally the login ID is saved as Username in the environment. Try opening the command window (Dos prompt) and typing “set” (without the quotes). This will give you a list of all the environment variables.

You then use Environ(Username) in your code to recover the username as a string.

HTH

Reply | Quote

Thanks David. In my experience the login ID is generally not available in the environment parameters except on NT machines. This could be overcome with a login script or batch but I feel it’s a bit too easily broken (or hacked).
I’m grateful for the idea though because I log them away and they come in handy later.

Very nice to see another Banana Bender in here. I’m also in Brisbane.

Reply | Quote

I use a very similar function with Access ’97 Win97-98 and
Win2000 with complete success. You should have no problem on your NT system.

Reply | Quote

Just found another problem. Andrew’s code works perfectly when the database is on the client machine but as soon as it (the database) is transferred to the server it no longer works because there is no local user and hence no user name.
Any ideas?

Reply | Quote

Maybe if you explained what you’re trying to accomplish you would get more focused answers. It is self-evident that a server doesn’t have a username, so what are you trying to accomplish from a server?

Reply | Quote

Strewth, you don’t beat around the bush do you. Ok more details – from the thread so far I would like to be able to know which NT user is working on the database. From the code that was supplied I can find the user name and use it in my database. This works fine while the database (front-end) is located on the user’s machine (my machine during building of the database). When the database is moved to the server (where I want it to live) I can’t use the code supplied to find the user’s name. Does this explain the predicament?
I’m thinking I might have to build a second back-end to reside on the user’s machine which will supply the user’s name to the front end but I don’t like the idea much.

Reply | Quote

Rob
Actually you still might want to think about the front-end back-end idea. Here is why…. typically its best to put your user interface elements in one MDB file and then keep the data in another and use Linked tables to get to the data. The real advantage is that if you have to update the application (forms, macros, querries, reports, etc) you don’t have to worry about keeping data in sync. Also if you distribute the file to the desktop machines it will load faster and your server and network guys will thankyou . I know its a pain to distrbute that way but hey attach it to an email (the mail guys will hate that ). Just remember to use a UNC (servernamesharename) to identify the location of the MDB file holding the tables. DO NOT use a mapped drive unless you are absolutely sure everyone who needs to use the app will use the same drive letter.

Glad the permissions thing solved your delema.
virtually
HR

Reply | Quote

My databae (plural of database?) are always front and back end as you describe although I usually build them as one database and split them as the last step before distribution. I’m still thinking about putting the front end on the clients machines but I’ve got to figure out how to update all the clients when there is a change. Charlotte also said there would be a big improvement in performance that way. I agree with your comments on mapped drives, they are a pain in the neck and I avoid them.

The help is greatly appreciated . I hope your question gets adequately answered. I couldn’t get a handle on the only attempted answer I have seen so far so I hope it was clearer for you.

Reply | Quote

Rob…
There is always a local user. Usually on a server it is the admin who is logged in on the server at the time. This user probably isn’t going to be listed in a user table so it may only appear to be broke..

Users accessing the MDB file via a share or UNC on the file server are still going to have their own Login ID when they access the MDB file over the network. In that case the posted code will work.

If the users are accessing the file from a web server then all the user rules change depending on how the web server is set up. If that is the case I will try to explain the best I can in a subsequent post.

One more thought, it may be that your users don’t have enough permissions on the file server. They need to have read, write, and delete because each time they access the MDB file using MS-Access an LDB file gets created. When they close MS-Access if no other users are using the MDB file then the LDB file will get deleted. If they can’t delete things can get strange.

Maybe one of the MVPs can explain what happens better.

Reply | Quote

Spot on! When I assigned full control for both the share and the folder security it works (I had assumed that’s how it was but I hadn’t checked it – the goose alarm has just gone off). The code is executed on the local machine and the user is correctly identified. Thank you and thanks Charlotte for bothering to read my convoluted question.

Reply | Quote

I think there is a bit of confusion here which I may have caused. The original question was how to get hold of and use the NT security key for checking the validity of a user’s input. I added to the question by asking how to get hold of the user name. My part of the question has been answered but the original question is still outstanding. The NT security key and the user login are two very different things. The user logs in with his name and password and then the NT Server supplies a security key to the workstation (Correct me if I’m off the beaten track). This is not easily accessed to my knowledge (not that my knowledge is very vast).

Reply | Quote