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

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"?

sa-17 user-management

asked 25 Mar, 08:25

Christian Ha...
697●13●18●33
accept rate: 42%

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

(25 Mar, 10:49) Volker Barth

flat view

One Answer:

active answers oldest answers newest answers popular answers

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

permanent link

answered 26 Mar, 02:39

adriancttnc
51●4
accept rate: 100%

edited 27 Mar, 08:36

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.

(26 Mar, 05:51) Volker Barth

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.

(26 Mar, 06:43) adriancttnc

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.

(26 Mar, 09:34) Chris Keating

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?

(27 Mar, 07:18) Christian Ha...

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.

(27 Mar, 08:31) adriancttnc

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!

(27 Mar, 09:40) Christian Ha...

More comments hidden

showing 5 of 6 show all flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

sa-17 ×246
user-management ×25

question asked: 25 Mar, 08:25

question was seen: 244 times

last updated: 27 Mar, 09:40

SAP SQL Anywhere

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

Follow this question

Related questions