How can you find out which connection(s) are impersonating as user X?

[Chris26]

Lets say user A is impersonating as user X using "setuser X".
In sa_conn_info, I still see "Userid = A".

select current user does show "X", but this obviously only works on the connection that issued "setuser X".

Is there any way you can tell from another connection, which connections are impersonating as user "X"?

[VolkerBarth]

I don't know whether this can be queried at runtime. The transaction log does contain the according SETUSER statement.

[former_SQLA_member1694983 · Accepted solution]

Does this help you?

I don't have time to investigate it as I'm caught with something else but I've seen your question in my daily digest email and I remember doing something similar.

Edit: Corrected version:

SELECT DISTINCT(number) as CONNECTION_NUMBER, connection_property('UserID', CONNECTION_NUMBER) as ACTUAL_USER, connection_property('CurrentUserID', CONNECTION_NUMBER) as USER_ALIAS, connection_property('Name', CONNECTION_NUMBER) as CONNECTION_NAME FROM sa_conn_properties()

Disregards this old one:

SELECT [YOUR_CONN_NUMBER_IF_NEEDED] as CONNECTION_NUMBER, connection_property('UserID', CONNECTION_NUMBER) as ACTUAL_USER, connection_property('CurrentUserID', CONNECTION_NUMBER) as USER_ALIAS, connection_property('Name', CONNECTION_NUMBER) as CONNECTION_NAME

[VolkerBarth · Accepted solution]

Ah, is "CurrentUserID" an undocumented connection property? I had searched for such a property but none is listed here for v17.0.11... - but apparently, sa_conn_properties() does list it, too.

[former_SQLA_member1694983 · Accepted solution]

I honestly don't know. I know I searched forums left and right until I've discovered it.

I'll try to lookup the source again but I've kept that bit of code above saved somewhere safe for future use.

[chris_keating · Accepted solution]

I just checked. CurrentUserID was added in the Fall 2022 but appears not to have been added to the documentation. I will ask the doc team to address this.

[Chris26 · Accepted solution]

We are on version 17.0.11.7254.
It doesn't seem work in that version.
Can you confirm that this was added in a later version?

[former_SQLA_member1694983 · Accepted solution]

Hi! We're using the exact same version.

I've just run this and I can confirm it IS working.

SELECT
    DISTINCT(number) as CONNECTION_NUMBER,
    connection_property('UserID', CONNECTION_NUMBER) as ACTUAL_USER,
    connection_property('CurrentUserID', CONNECTION_NUMBER) as USER_ALIAS,
    connection_property('Name', CONNECTION_NUMBER) as CONNECTION_NAME
FROM sa_conn_properties()

I now realise what I have posted before doesn't work if no connection number is provided. My apologies for that.

[Chris26 · Accepted solution]

Ahh... the db service was on an older version then my local Sybase Central version I was testing it from.
This is exactly what I was looking for.
Thanks a lot!