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

asked 25 Mar, 08:25

Christian%20Hamers's gravatar image

Christian Ha...
697131733
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

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's gravatar image

adriancttnc
514
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
1

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
3

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
1

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

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

Question tags:

×246
×25

question asked: 25 Mar, 08:25

question was seen: 197 times

last updated: 27 Mar, 09:40