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 :

@Victoria What do you mean ? Run a query to get the current user ? — user763539, Apr 24 '18 at 06:07
Yep. I went through the API reference and couldn't find anything related to this. Even for SDAC. — Victoria, Apr 24 '18 at 06:09
yes,I suppose this could be done by a stored procedure. Thank you Victoria. — user763539, Apr 24 '18 at 06:16
Read the [docs](https://learn.microsoft.com/en-us/sql/t-sql/functions/current-user-transact-sql?view=sql-server-2017) — Ilyes, Apr 24 '18 at 08:16
Take a look at the results of `EXEC sp_helpuser`. Can be helpful to know the role the user has for the database and adapt the UI to it etc. — Brian, Apr 24 '18 at 17:51

Victoria · Accepted Answer · 2018-04-24T10:01:59.970

2

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;

edited Apr 24 '18 at 10:01

answered Apr 24 '18 at 06:44

Victoria

7,822
2
21
44

Tested in Delphi 2009, UniDAC 7.2.7 against Microsoft SQL Server Express 12.0.2000.8. Well, I don't have source of UniDAC, but looking at [the reference](https://www.devart.com/unidac/docs/devart.dac.tcustomdaconnection.execsql(system.string,system.object[]).htm), I was hoping that the `Result` output parameter will be predefined. But it wasn't, so I smell something wrong around that parameter. Maybe try to execute only this `UserName := UniConnection1.ExecSQL('SELECT :Result = CURRENT_USER');`. Anyway, don't you want use a separate query object? – Victoria Apr 24 '18 at 14:59
It's worth adding I'm not keen in UniDAC. I'm just certainly sure there is no UniDAC API and that you need to fetch this information by SQL command. – Victoria Apr 24 '18 at 15:17
Well, could you try [this variant](https://pastebin.com/1ny41FTD) (which uses for output user defined parameter)? – Victoria Apr 24 '18 at 18:44
tried the variant but still get the same error :( I have Delphi Tokyo 10.2.3 with Uniddac 7.2.5 – user763539 Apr 25 '18 at 02:41
Try at least getting the `CURRENT_USER` function statement working. Could you try executing those two SQL commands from this answer with a `TUniQuery` component? At least we could narrow the problem a bit. The error message you get sounds like a bug around parameters in `ExecSQLEx` method. – Victoria Apr 25 '18 at 10:34

score 0 · Answer 2 · answered Apr 24 '18 at 06:29

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).

You don't have to test any of these. All of them returns login name, not user name. — Victoria, Apr 24 '18 at 11:45

obtain the real identity of the connected user

2 Answers2