Find username for current session

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Find username for current session

Author

Topic

mb
Starting Member

16 Posts

Posted - 2002-04-03 : 07:46:49

Is there any way to find out the username for the current session. The current user will have dbo privileges.

This code snippet always seems to return dbo and not the NT login.

SELECT 'The current user is: '+ convert(char(30), CURRENT_USER)

dsdeming

479 Posts

Posted - 2002-04-03 : 08:20:39

Run this to see 3 different user names that are available:

SELECT USER_NAME(), CURRENT_USER, SYSTEM_USER

SYSTEM_USER may be the one you're after.

Teroman
Posting Yak Master

115 Posts

Posted - 2002-04-03 : 08:39:00

select sUser_sName()

returns

Domain\NTUserName

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-04-03 : 11:43:14

From what I can tell, if you are using NT Authentication, the system_user is the Domain\Username as Teroman suggests. If you use SQL authentication, the system_user returns only the Username. In the sysusers table, the name is the Username alone without the Domain.

I created the following to return the username, uid depending on how the user is logged into SQL:

SELECT Case
When charindex('\', SYSTEM_USER) > 0 then User_ID(SUBSTRING(SYSTEM_USER, charindex('\', SYSTEM_USER)+1, Len(SYSTEM_USER)-charindex('\',SYSTEM_USER)))
Else user_ID(system_User)
End as user_ID,
Case
When charindex('\', SYSTEM_USER) > 0 then SUBSTRING(SYSTEM_USER, charindex('\', SYSTEM_USER)+1, Len(SYSTEM_USER)-charindex('\',SYSTEM_USER))
Else system_User
End as user_name

Hopefully someone can verify that this does work for both types of logins and does not contain any apparent flaws. It seems to work in what I am doing.

Jeremy

wblackmon
Starting Member

3 Posts

Posted - 2002-04-04 : 16:28:22

Thanks Jeremy.. That does exactly what I need it to do!
Now this will help me be able to figure out who is changing data...

Will Blackmon
Information Technology
Bank of Alabama

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources