I have the exact Query Text and the exact time & date the Query was executed, how can I find the Host Name that executed this query?
I'm using SQL Server 2008.
Asked
Active
Viewed 5.3k times
3
-
When you say Host Name, are you thinking Web Server? – John Cappelletti Jun 24 '16 at 12:40
-
Do you want `SELECT @@SERVERNAME`? – Arulkumar Jun 24 '16 at 12:42
-
I guess that OP needs host name of the machine from which the query was executed. – gofr1 Jun 24 '16 at 12:49
-
you can use Sql profiler too. – Ajay2707 Jun 24 '16 at 13:13
4 Answers
8
Do you need @@SERVERNAME
SELECT @@SERVERNAME
will return the server name where the query was executed.
HOST_NAME will return the workstation name
SELECT HOST_NAME()
Arulkumar
- 12,966
- 14
- 47
- 68
-
this we know for sure, I'm asking about the host name or the computer name that executed this query – Shahim Khlaifat Jun 24 '16 at 12:53
1
You can check with HostName() as
SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser
http://blog.sqlauthority.com/2009/05/26/sql-server-find-hostname-and-current-logged-in-user-name/
or
SELECT @IP_Address = client_net_address
FROM sys.dm_exec_connections
WHERE Session_id = @@SPID;
How to get the client IP address from SQL Server 2008 itself?
How to identify the caller of a Stored Procedure from within the Sproc
1
There is not any table with historical information about the host that executed a query - http://www.sqlservercentral.com/Forums/Topic1334479-146-1.aspx
Andriy Tolstoy
- 5,690
- 2
- 31
- 30
-
the query that write before gives most likely accurate results – Shahim Khlaifat Jun 24 '16 at 14:57
-
The key point is that `sys.dm_exec_requests` returns information about requests that are **executing** and `master.dbo.sysprocesses` contains information about processes that are **running** now. – Andriy Tolstoy Jun 24 '16 at 15:08
-
-
1I don't think they do unless you have got a profiler running at that time. – Andriy Tolstoy Jun 25 '16 at 08:30
0
This is as close as you'll get, I believe:
select host_name()
As is mentioned in the docs: "The client application provides the workstation name and can provide inaccurate data. Do not rely upon HOST_NAME as a security feature."
Tim Lehner
- 14,813
- 4
- 59
- 76
-
this will give me my host name, please read the question again – Shahim Khlaifat Jun 24 '16 at 12:57
-
I'm not asking about my host name, please read the question again – Shahim Khlaifat Jun 24 '16 at 12:58
-
@ShahimKhlaifat Are you executing the query on the server itself? If you're going through a web app, the host_name() will return the web server. – Tim Lehner Jun 24 '16 at 13:43
-
Starting in SQL Server 2016 [Host_Name()](https://learn.microsoft.com/en-us/sql/t-sql/functions/host-name-transact-sql?view=sql-server-ver15) will return the name of the workstation. Prior to that I think it returned something less useful. – Ben Apr 09 '20 at 04:39