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.

SELECT *

FROM
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'

Henrik Staun Poulsen

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...

HIH,
Henrik

countrymeister

ASKER

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.

John_Vidmar

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.

ASKER CERTIFIED SOLUTION

Henrik Staun Poulsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

One or more of these functions should be able to help:

HOST_ID ()
HOST_NAME()
ORIGINAL_LOGIN()

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.