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)
pinaldave
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