Link to home
Start Free TrialLog in
Avatar of countrymeister

asked on

How can I find the host name, machine name from where a query was executed

An update was made on a table in database BAC.  I know the approx time this happened.
I need to find out from which machine this  query was made.

The issue is we all use the same userid/password to log in. So the SSID does not help.

I looked into the transaction logs but was not successful. I do have sa privileges.


    fn_dblog(NULL, NULL)
WHERE    [Operation] = 'LOP_BEGIN_XACT'
    AND [Begin Time]  >= '2014/05/07 05:35:23:053'
 AND [Begin Time] <= '2014/05/07 06:35:23:053'
Avatar of Henrik Staun Poulsen
Henrik Staun Poulsen
Flag of Denmark image

That is why we switched to Integrated Security some years back.
On most databases I have a database trigger, so that I can see who changed that stored procedure which no longer works...

Avatar of countrymeister


Thanks Hsspoulsen.

I do understand the shortcomings with having the same userid/pwd. And I do have reasons why ths was done.
Bur as things stand, I need a solution to detect the host name/machine name.
sp_who is a stored-procedure that displays current users on your database, including hostname.  You can review the code behind sp_who as follows:
sp_helptext 'sp_who'

I use that to derive my own queries, shows which system-tables were used in formulating the results.
Avatar of Henrik Staun Poulsen
Henrik Staun Poulsen
Flag of Denmark image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
One or more of these functions should be able to help:


You could also have each login specify something in CONTEXT_INFO() at initial connection that identified a particular user, but of course that would require coding changes.