How to current logged windows user name in tsql

_{Monday, March 27, 2006 9:02 PM}

Hello

how can i get current logged windows user name in tsql. I know with windows aut it is possible how can i get this when sql auth.

Is there any system function or any special codeing required ?

regards

Chikuu

_{Thursday, May 29, 2008 11:22 PM ✅Answered | 8 votes}

This seems to work

DECLARE @sys_usr char(30)

SET @sys_usr = SYSTEM_USER

SELECT 'The current system user is: '+ @sys_usr

GO

_{Monday, March 27, 2006 9:05 PM | 3 votes}

take a look at suser_sname()

select user_name(),suser_sname()

Denis the SQL Menace

http://sqlservercode.blogspot.com/

_{Monday, March 27, 2006 9:08 PM}

Hello

it gives current sql login name i want current windows user name.

e.g if sql login name is sa and current windows user name is chikku then it should return chikku not sa

Regards

Chikku

_{Monday, March 27, 2006 9:12 PM}

That means the person is login in with sql authentication not windows authentication

_{Monday, March 27, 2006 9:21 PM}

Hello

I logged on my system as Chikku, but i am accessing sql server thorugh sa login in this case i want to retrive current windows user. In my application we had created special login through all users are accessing sql server from diffrent terminals i want read particualr terminals name suppose 'John' inserted some rows then how can determine that who had inserted rows.

Regards

Chikuu

_{Tuesday, March 28, 2006 5:42 AM | 1 vote}

I think the answer is: you can't (exactly).

You might be able to use sysprocesses or dm_exec_requests (or sessions) to determine the computer they were on.  Most people implement a database based login system of their own when they use a common login (best to use windows authentication and application roles if you want to get this kind of information "automatically"

Another possibility is to use the context_info and manage setting the user yourself.  You can then read the value using the CONTEXT_INFO function in 2000:

DECLARE @BinVar varbinary(128)
SET @BinVar = CAST('I am user dude' AS varbinary(128) )
SET CONTEXT_INFO @BinVar

SELECT cast(CONTEXT_INFO() as varchar(30)) AS MyContextInfo
GO

On 2000 you had to use the sysprocesses table:
-- Select context information set several batches earlier.
SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @@spid
GO

 

_{Tuesday, February 26, 2013 4:53 PM}

It does not work. It may appear to if you try it in SSMS without changing connection - I also would like to know the correct answer