SQL SERVER – Find Hostname and Current Logged In User Name

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

SQL SERVER - Find Hostname and Current Logged In User Name loginsa

2) Logged in using Windows Authentication

SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser

SQL SERVER - Find Hostname and Current Logged In User Name loginwin

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)

Solarwinds
, , ,
Previous Post
SQLAuthority News – Download Microsoft SQL Server 2008 Books Online (May 2009)
Next Post
SQL SERVER – Interesting Observation of Logon Trigger On All Servers

Related Posts

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

    Reply
  • abhIShek online4all
    October 17, 2011 6:30 pm

    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

    Reply
    • 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

      Reply
  • 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”

    Reply
  • 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

    Reply
  • 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?

    Reply
  • Hi Pinal ,

    How I can find the logs of Sql server ,when Username is same and hostname is different .

    Reply
  • how to get ip address and user name?

    Reply
  • Hi Pinal,
    I like to store user’s hostname/IP who run any SQL commend on any specific database. Please guide me.

    Reply
  • 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

    Reply
  • HI Pinal,

    How I can get the Windows UserName, if I logged in as sa (sql authentications)?

    Thanks,
    Ashish

    Reply
  • Mahesh Deore
    July 31, 2015 3:27 pm

    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…

    Reply
  • Mahesh Deore
    July 31, 2015 3:52 pm

    I got Solution on this:
    Just use below query

    select Serverproperty( ‘MachineName’ ) as MachineName

    Reply
  • 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

    Reply
  • 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?

    Reply
  • hello

    do sql saves host name at the back end???

    Reply
  • 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

    Reply

Leave a Reply

Menu