Avatar of countrymeister
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.

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'
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Henrik Staun Poulsen

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.