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

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 (http://blog.SQLAuthority.com)

About these ads

33 thoughts on “SQL SERVER – Find Hostname and Current Logged In User Name

  1. Hi Pinal,

    We can also know the Host Name, Logged-in user name, current Sql Server & DB Name(which we are accessing) from the Status Bar of the Sql Sever Management Studio, without running the scripts. So, is there any other special reasons for which the above scripts can be used?

    Please clarify us on this.

    Thanks,
    Vinay

    Like

  2. I think a good addition this for insert/update/delete statements would be

    IF HOST_NAME() = ‘myhost’ AND SUSER_NAME() = ‘myuser’
    BEGIN
    — Perform update here
    END

    This should neatly eliminate accidental inserts/updates which occur all too easily, especially when you have the same environment on multiple servers (ie. dev/test/prod servers)

    Like

  3. @vinay

    These scripts are used in front end application that access backend database.

    This is just for the display at front end.

    Good Example could be, when you log into your yahoo mail or gmail, you see , logged in as your user_id.

    The same functionality is used in many front end application designed on .NET or VB. Since users do not access data through Management studio. These scripts are embedded in front end application, just for visualize purpose.

    Secondly, for auditing purpose we use these scripts to feed default values into tables.

    we just create a table like this,

    Create table TAuditing_Logs ( AuditID int Identity, ObjectName varchar(128) , Changed_By varchar(128) default (Suser_sname()) , Modified_Date datetime default ( getdate())

    If any user changed any table structure and there is a ddl trigger defined on that table, then Suser_sname(), could define who changed that table, getdate() can give details when the table was changed…

    There could be many other occasions where we could use these scripts.

    ~ IM.

    Like

  4. Thanks Pinal, this post helped me to give some database level permissions using triggers for creating/altering/dropping of tables/procedures. I tried the “deny create procedure to ” and “revoke create procedure to “statements to avoid the user from creating stored procedures, but they didn’t worked out as the user is having the sysadmin Server Role. If I remove the sysadmin role, that will cause nothing to work for this user. Even the select statement will not work. I tried other Server Roles too, but nothing worked out. Is there anything that I am missing out or any other way to do the same without using triggers? Thanks in advance.

    Like

  5. Hi there,

    Thanks for the really informative posting.

    I do have one related question.

    Is there an easy to determine the role(s) of the currently logged on user without using sp_helpuser as this returns all roles including server roles. I am only interested in database roles.

    Thanks,
    Robert

    Like

  6. CREATE PROC USP_CHECK_DATABASE_ROLE_USERS
    AS
    SET NOCOUNT ON
    CREATE TABLE #TEMP1 ( UID INT, NAME SYSNAME)
    CREATE TABLE #TEMP2 ( ID INT IDENTITY ,UID INT, NAME SYSNAME)
    CREATE TABLE #TEMP3 ( USER_NAMES SYSNAME, ROLE_NAMES SYSNAME)
    INSERT INTO #TEMP1 SELECT UID, NAME FROM SYSUSERS WHERE SID NOT IN (0X00 , 0X01) AND SID IS NOT NULL
    INSERT INTO #TEMP2 SELECT UID, NAME FROM SYSUSERS WHERE SID IS NULL
    INSERT INTO #TEMP3 SELECT A.NAME USER_NAMES , B.NAME ROLE_NAMES FROM #TEMP1 A, #TEMP2 B , SYSMEMBERS C WHERE A.UID = MEMBERUID AND B.UID =GROUPUID

    DECLARE @VAR INT
    DECLARE @VAR2 VARCHAR(128)
    DECLARE @CMD VARCHAR(8000)

    SET @VAR = 1
    SET @CMD = ‘SELECT USER_NAMES,’
    WHILE @VAR < = ( SELECT COUNT(*) FROM #TEMP2 )
    BEGIN
    SELECT @VAR2 = NAME FROM #TEMP2 WHERE ID = @VAR

    IF @VAR =1
    BEGIN
    SET @CMD = @CMD + ' MAX(CASE ROLE_NAMES WHEN '+''''+@VAR2+'''' +' THEN ''Y'' ELSE ''X'' END ) AS ['+UPPER(@VAR2)+ ']'
    END

    IF @VAR 1 AND @VAR ( SELECT COUNT(*) FROM #TEMP2)-1
    BEGIN
    SET @CMD = @CMD +’ , ‘ + ‘ MAX(CASE ROLE_NAMES WHEN ‘+””+@VAR2+”” +’ THEN ”Y” ELSE ”X” END ) AS [‘+UPPER(@VAR2)+ ‘]’
    END

    IF @VAR = ( SELECT COUNT(*) FROM #TEMP2)
    BEGIN
    SET @CMD = @CMD +’ , ‘ + ‘ MAX(CASE ROLE_NAMES WHEN ‘+””+@VAR2+”” +’ THEN ”Y” ELSE ”X” END ) AS [‘+UPPER(@VAR2)+ ‘]’+’ FROM #TEMP3 GROUP BY USER_NAMES ‘
    END
    SET @VAR = @VAR+ 1
    END
    EXEC (@CMD)
    DROP TABLE #TEMP1
    DROP TABLE #TEMP2
    DROP TABLE #TEMP3
    SET NOCOUNT OFF

    Like

  7. Pinal,
    We can use Serverproperty(‘MachineName’) to see exactly which server you are connected to. And when we run above query it will give my laptop name whichever server I connected.

    Like

  8. Hi Pinal,
    Like Vishwa I am looking at getting either the windows username or the computer name of the user. My front end is an Access adp, and I get the above information using Environ(“USERNAME”) and Environ(“COMPUTERNAME”), but I don’t know how to get it from SQL server to use it in a trigger.
    Thanks for your help

    Like

  9. Hi Pinal,

    Is there a way to change a hostname connection string from a active connection?

    For example:
    I have a unique login into the SQL Server that it’s used by my aplication. So, I have a lot of connections ( … count(*) from sys.sysprocesses … ) with the same strings, like below…

    spid ecid status loginame hostname blk dbname cmd request_id
    —— —— —————————— —————————————————————————————————————————- ——————————————————————————————————————————– —– ——————————————————————————————————————————– —————- ———–
    105 0 sleeping appuser_0027 ares13 0 dados AWAITING COMMAND 0
    110 0 sleeping appuser_0027 ares13 0 dados AWAITING COMMAND 0
    184 0 sleeping appuser_0027 ares13 0 dados AWAITING COMMAND 0
    185 0 sleeping appuser_0027 ares13 0 dados AWAITING COMMAND 0
    186 0 sleeping appuser_0027 ares13 0 dados AWAITING COMMAND 0
    187 0 sleeping appuser_0027 ares13 0 dados AWAITING COMMAND 0
    195 0 sleeping appuser_0027 ares13 0 dados AWAITING COMMAND 0
    202 0 sleeping appuser_0027 ares13 0 dados AWAITING COMMAND 0
    203 0 sleeping appuser_0027 ares13 0 dados AWAITING COMMAND 0
    259 0 sleeping appuser_0027 ares13 0 dados AWAITING COMMAND 0

    This way, I cannot know who is under each connections/processes (local machine or people).

    Best Regards…

    Jorge Silva / Brazil.

    Like

  10. While connecting through a ssh tunnel that tunnels localhost:1433 to remotehost:1433 through host2, “select host_name()” returned “localhost” instead of “remotehost”. However, SERVERPROPERTY(‘MachineName’) returned the correct value.

    Like

  11. Using SQL Server 2000, I only find that
    SELECT SYSTEM_USER identifies the current logged in user.
    None of the queries above work for me.

    Like

  12. Hi everyone,
    I am trying to run an update script on several databases. The condition is I can only run the script if there is no user connected to the database. I have tried using the sysprocesses and doing count of the spid. But it’s not working. Any help is appreciated.
    Thanks,
    Sushma

    Like

  13. Hello,
    i’m new in programming.
    i’m trying to connect to SQL Server Management Studio with NetBeans 6.9 but where could i find :
    -Host name?
    -Port?
    -instance name?

    Thank you
    SEG

    Like

  14. Thanks

    i find out with:

    select HOST_NAME ()
    select @@ServerName
    and the Port with the configuration

    But i have the following message when i try to connect to the database with Netbeans 6.9:


    Unable to add connection. Cannot establish a connection to
    jdbc:sqlserver://Hostname\nom-de-ordinateur\SQLEXPRESS:1433; databaseName=Master using com.microsoft.sqlserver.jdbc.SQLServerdriver (The TCP/IP connection to the host has failed, java.net.ConnectionException: Connection refused: connect).

    Why? i don’t know, please help-mE@

    Th@nks

    Like

  15. Hello pinal,

    I want to retrieve windows nt login name from sql query. My server is installed as mixed mode(sql server and windows).
    Can you please tell me how can i get result?

    Thanks
    Sambhu

    Like

  16. Good stuff. Now that I know how to get the host_id, I want to send a message to the host calling the proc under certain situations. I’m on Server 08 and no ‘Net Send’. I’ve installed WinSentMessenger but haven’t been able to make it send using SQL code. Supposedly, WinSentMessenger is supposed to work with Net Send commands and using an XP box with Messenger started, the Net Send commands work.

    Email is not an option as the workstations calling the proc will not have email access.

    Thanx,
    Wallace

    DECLARE @hostid varchar(20)
    SET @hostid = (SELECT HOST_ID())
    –print @hostid
    DECLARE @SQLmsg nvarchar(2000)
    SET @SQLmsg = ‘net send’ + ‘ ‘ + @hostid + ‘ ‘ + ‘my_message’
    exec xp_cmdshell @SQLmsg –, no_output

    Like

  17. how to get hostname and IP of a machine on a citrix or remotedesktop environment on sql server

    eg i use a program hosted on citrix environment say excel to connect to my sql server and change some values . i want to know this user on this machine connected to the citrix server to connect to the database and make these changes.

    I created an audit trigger which is fired when any change is made to the table and it is properly recording the changes . the question is which machine/ ip address did the user use to login to citrix to do this task .

    SELECT @@SPID AS ‘ID’, SYSTEM_USER AS ‘Login Name’, USER AS ‘User Name’,

    select client_net_address,local_net_address
    from sys.dm_exec_connections where session_id=@@SPID

    will still return citrix server how to get the user’s computer

    Like

  18. How to get the Server Name (SQLServer 2005) without installing SQLServer 2005 client (Query Analyser) i.e. without use of any SQL Query.

    Like

  19. Hello Pinal:

    Is it possible to know who are all accessed the DB, what were the queries they have ran? It should give the results from the DB creation time?

    Like

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

    Like

    • 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

      Like

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

    Like

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

    Like

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

    Like

  24. Pingback: SQL SERVER – Weekly Series – Memory Lane – #031 | Journey to SQL Authority with Pinal Dave

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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s