Closed TheConstructor closed 2 years ago
@TheConstructor it would require a lot of changes to make adding this column to the output as useful as login_name
, WRT filtering, and ordering, etc. Even without that, it's a whole bunch of changes to respect formatting and desired columns, etc. It seems like you're aware that the base query hits sys.sysprocesses
, and then sys.dm_exec_sessions
later, so I won't go into how that might make things weird (and potentially incorrect).
It's maybe an option to replace the column in the select list from sys.sysprocesses
with the ORIGINAL_LOGIN()
function, which would only require a single parameter and some case logic, but I don't have much experience using that function, so I'm not aware of any shortcomings or side effects in using it. If you do, please let me know.
If I don't hear back from you in about 30 days, I'm gonna close this out, though.
Thanks!
Have you been referring to this ORIGINAL_LOGIN()? It's a great function, but can only return the information from the currently executing context, and doesn't offer any parameters I am aware off.
I can try to come up with something, though I am certainly not programming a lot off the clock right now, so it may take some time. What would be hard requirements for a PR? Compatible with 2005+? Anything else?
This is all set - I added it to the additional_info collection.
@amachanic Thank You!
While not available through
sys.sysprocesses
,original_login_name
is a very handy column e.g. insys.dm_exec_sessions
(orsys.dm_pdw_nodes_exec_sessions
in warehouse). We use application roles frequently and some legacy code usesEXECUTE AS
with dynamic SQL. Especially in the later caseoriginal_login_name
offers invaluable information.