obtain the real identity of the connected user

Question

dxStatusbar1.Panels1.Text := DataModule2.UniConnectDialog1.Connection.Username;

...gives me the username that has connected to sql server. However the connected user has a different name in the actual database.

Example: His login name for the sql server is 'John' and is user mapped to 'Northwind' database. However in 'Northwind' database he is called 'John Smith'. And this is the name (John Smith) I am trying to have displayed in dxStatusbar1.Panels1.Text after he connects.

How can I get that ?

edit : Tried Victoria suggestion :

UserName := DataModule2.UniConnection1.ExecSQL('SELECT :Result = CURRENT_USER', ['Result']);
 dxStatusbar1.Panels[1].Text := UserName; 

but get :

Answer 1

I couldn't find any UniDAC API way to get currently connected user name (not even for SDAC), so I would just issue a SQL command querying CURRENT_USER and grab the name from the result:

SELECT CURRENT_USER;

Or in the Unified SQL way with the USER function:

SELECT {fn USER};

Since you've mentioned stored procedure in your comment, it sounds to me like you probably want to get this information directly from a connection object without using query object. If that is so, you don't even need to have a stored procedure but execute directly command like this:

var
  UserName: string;
begin
  UserName := UniConnection1.ExecSQL('SELECT :Result = CURRENT_USER', ['Result']);
  ...
end;

Or in unified way:

var
  UserName: string;
begin
  UserName := UniConnection1.ExecSQL('SELECT :Result = {fn USER}', ['Result']);
  ...
end;

Answer 2

One of these might do the job for you. Haven't tested.

SELECT ORIGINAL_LOGIN()
SELECT SYSTEM_USER
SELECT SUSER_SNAME()

Hope it helps.

ORIGINAL_LOGIN: Returns the name of the login that connected to the instance of SQL Server. You can use this function to return the identity of the original login in sessions in which there are many explicit or implicit context switches.

SYSTEM_USER: Allows a system-supplied value for the current login to be inserted into a table when no default value is specified.

SUSER_SNAME: Returns the login name associated with a security identification number (SID).