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,
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
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)
@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.
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.
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
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
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.
hi,
can any one please help me out in getting windows username when logged into server (Server Authentication)
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
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.
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.
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.
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
@Sushma Shah
Did you get the answer you need?
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
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
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
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
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
How to get the Server Name (SQLServer 2005) without installing SQLServer 2005 client (Query Analyser) i.e. without use of any SQL Query.
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?
There are at least two options
1 Sp_who
2 Profiler