How to define a current user?

Question

Is it possible to define a current user?

I found a stored procedure 'sp_mgGetConnectedUsers'. It returns a result set with the only unique field 'Address'. How could I associate an executing query with such 'Address'.

Please advice.

Note: As far as I understand, another way to get the current user is to set a unique application Id for each connection, but I don't like this way much.

Answer 1

There are multiple ways to think about the current user. It depends on how you use Advantage.

The sp_mgGetConnectedUsers output gives the following:
UserName - Computer Name
Dictionary User - User used to login to the ADS Dictionary
Address - IP, IPX or IPC address
OSUserLoginName - Client OS login name
TS Address - IP address of the end-user of the client address
ApplicationID - Uniquely setable ID default=exe name

If you use a Dictionary and each person connects the Dictionary with a Unique Name then Dictionary User would be the way to go (you can also get this by "select user() from system.iota" (or ) for sql scripts etc). Address and UserName will be the IP Address of the user and Computer Name of the user. This should help you uniquely identify the user as well. If your users are coming in via Terminal server you can use the TS Address to identify them in combination with the OSUserLoginName. I won't go into detail on ApplicationID since you specifically stated that you didn't care for this solution. But... While you can set this to anything you want the default is the name of the application (i.e. ARC.exe)

Part B of your question is how to associate a query. The sp_getsqlstatements procedure will return the current active queries (or you could use Remote Server Info in ARC). This returns the Connection Name which is the same as UserName from sp_mgGetConnectedUsers. Using the combination you can tell the Computer Name, IP address (or Terminal Adderss), Dictionary UserName, Client OS LoginName, and the application name.

Answer 2

If using a data dictionary you can use the user scalar function:

select user() from system.iota;

and correlate that with the DictionaryUser field returned from sp_mgGetConnectedUsers.

If not using a dictionary the easiest way would be to use the sp_SetApplicationID and sp_GetApplicationID. Why don't you like that way? It might be easier to help you if we knew what your final goal was. If you just want to see the user associated with a query, execute the sp_GetSQLStatements procedure.