I work in an environment wherein I connect to multiple servers across the world. Time and again, my SSMS is connected to a myriad of servers that kindles a lot of confusion. I frequently use the following trick to separate different connections, which I mentioned in my blog sometime back SQL SERVER – 2008 – Change Color of Status Bar of SSMS Query Editor. However, this trick does not help when a huge number of different connections are open. In such a case, I use the following handy script. Do not go by the length of the script; it might be very short but always works great!
Now, let’s take a look at the execution of this script in two different scenarios.
1) Logged in using SQL Authentication
SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser
2) Logged in using Windows Authentication
SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser
It is quite evident from both the above cases that we get correct logged-in username and hostname. Let me know if this script is helpful to you when you face a similar situation.
Reference : Pinal Dave (https://blog.sqlauthority.com)
43 Comments. Leave new
Hi Pinal… I’m stuck in a scenario and hope to get some hint/help here.
I have a shared login used by multiple users and someone dropped a table.
I want to identify – which IP dropped the table & when? WHEN is important as compared to IP because the machine is in the cloud and people normally logon to the cloud server for access. If I can get IP of host computer, that is WOW!
Any help on this matter is highly appreciated.
Hi Pinal,
I got Clientprocessid (Host_ID) from sql profiler. how can i know the Host Name from that id?
Thanks in advance,
abhIShek Online4all
You can include HostName column as part of profiler result. Open the profiler goto File–>Templates–>Edit templates–>Event selections–>check show all columns–>select HostName
How to get host name along with domain name in SQL .I have a SQL server installed in a machine named “TestMachine.domain.com but when I use HOST_NAME or server property I am able to retrieve only “TestMachine”
how to show the message after updating in store procedure in sql 2008
EG:- Message as : Column value changed from XXXX to YYYY for the ZZZZ
XXXX is the old value
YYYY is the new value
ZZZZ is for in which tab the value is changed. these both XXX,YYY and ZZZ want to display after updatein..
Please give idea
Hi Pinal,
In one of my application i need IP address of the client from Sql server 2000. I know i can do xp_cmdshell but the user by which our application connects does not have rights. Any other way to get IP from sql server 2000 TSql?
Hi Pinal ,
How I can find the logs of Sql server ,when Username is same and hostname is different .
how to get ip address and user name?
Hi Pinal,
I like to store user’s hostname/IP who run any SQL commend on any specific database. Please guide me.
HI Pinal,
We can find out the the computer Name (Host Name) using HOST_NAME() in SQL SERVER, in the same way how could we find out the name of the user who have logged into the machine/OS.. if could we see that means it will be helpful in the audit.
Because with the same SQL login more no people could connect with SQL SERVER right, in this case we can only check with HOST_Name() to filter the people, if we have user login name of the windows means it will be so helpful
HI Pinal,
How I can get the Windows UserName, if I logged in as sa (sql authentications)?
Thanks,
Ashish
SELECT HOST_NAME() would give the machine name on which the sql server is running, but i want the HOST_NAME of actual machine where sql server installed.
Please help ASAP…
I got Solution on this:
Just use below query
select Serverproperty( ‘MachineName’ ) as MachineName
useful query that I gleaned somewhere on the web that answers (parts of) various questions above:
SELECT es.program_name, es.host_name,
es.login_name, COUNT(ec.session_id) AS [connection_count]
FROM sys.dm_exec_sessions AS es
INNER JOIN sys.dm_exec_connections AS ec ON es.session_id = ec.session_id
GROUP BY es.program_name, es.host_name, es.login_name
Tim, thanks for sharing.
So, I have a little different situation. I have a User table for application in my db. John Smith network login is netjsmith, but his username, that he uses to log into app in the User table is josmith1. Is there a way that it would capture the username from the User table?
I forgot to mention I am doing this for auditing purposes
hello
do sql saves host name at the back end???
I have installed an attendance system of matrix an its sql db server is need to be in domain but when I tried to put it in a domain the matrix system stops working…
pls do suggest me.. what should I do
select SERVERPROPERTY(‘computernamephysicalnetbios’)
This will helps to find on which host system our sql server is running Currently
Pinal and Tim, thanks for sharing.