November 9, 2009 at 2:17 am
Hi,
I need to write a trigger to write in a table the LogonId of windows user responsible for a SQL operation.
(Note that LogonId is the same value returned, for example, from command WMI Win32_Session) .
How can I do ?
Thank
November 9, 2009 at 4:21 am
gdraghetti (11/9/2009)
I need to write a trigger to write in a table the LogonId of windows user responsible for a SQL operation.(Note that LogonId is the same value returned, for example, from command WMI Win32_Session).
DML trigger: ORIGINAL_LOGIN() or SUSER_ID()
DDL trigger: EVENT_DATA.
Trace.
Event Notifications.
November 9, 2009 at 6:45 am
Thank you for response.
I tried with ORIGINAL_LOGIN() and SUSER_ID() but not return the value specified number of LogonId.
November 9, 2009 at 8:15 am
i think the best you can get from inside sql server is the NT Username as provided from original_login()
check the sysprocesses table to see if something in there interests you
select * from master..sysprocesses where spid = @@spid
--
Thiago Dantas
@DantHimself
November 9, 2009 at 8:46 am
Thank you for your response, but the problem is not solved.
LogonId is necessary because ORIGINAL_LOGIN() is insufficient (for example: two windows user name equal in contemporary Terminal Server sessions).
Sysprocesses table does not contain anything useful in this regard.
November 9, 2009 at 9:18 am
i think i know what your going for, you want to differentiate terminal services logins using the sql server. don't think its possible through T-SQL
--
Thiago Dantas
@DantHimself
November 11, 2009 at 6:00 am
What about...
select original_security_id from sys.dm_exec_sessions where session_id = @@spid
...?
You might need to mangle the binary a bit. Other than that, no idea.
Paul
November 12, 2009 at 12:58 am
I will do other tests and research.
Anyway thanks to everyone for the answers.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply