Query For Detecting Windows Username

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 2005 Forums

Transact-SQL (2005)

Query For Detecting Windows Username

Author

Topic

hanifbarik
Starting Member

9 Posts

Posted - 2009-01-29 : 08:03:34

Is there a variable or function in SQL Server 2005 which detects the Windows User Name of the person running a query. I have a client application which passes queries and I would like to detect the Windows User Name of the machine running the client application.

Any help would be appreciated. Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 08:07:36

It depends.
How do they log into the database? SSPI? If so, use SUSER_SNAME() function.

E 12°55'05.63"
N 56°04'39.26"

hanifbarik
Starting Member

9 Posts

Posted - 2009-01-29 : 08:28:18

Thanks. Just to clarify...

I have two tables:

STAFF table

ID NAME GROUP
1 John Smith Audit
2 Karl Taylor HR
3 Bryan Mills Audit
4 Susan Bennett Audit

USER table

ID USERLOGON STAFF_ID
...
256 jsmith 1
257 kTaylor 2
258 BMills 3
259 SBennett 4

I would like to pass a query which returns the staff NAME and GROUP based on the user name which matches it to what is stored in USERLOGON. I have the following query:

select NAME, GROUP
from USER
join STAFF on ID=STAFF_ID
where USERLOGON = 'jsmith'

I would like to replace 'jsmith' with a function which detects the user name.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 09:17:50

Great!
Did you read my previous response? Do you have a comment on that?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-29 : 09:23:13

see this

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/ed8e93f8-5841-4518-9065-8b650700eee8/

hanifbarik
Starting Member

9 Posts

Posted - 2009-01-29 : 12:30:55

quote:
Originally posted by Peso

Great!
Did you read my previous response? Do you have a comment on that?

Thanks Peso. As you mentioned, suser_sname() returns the SQL Server user. My users connect using Windows Authentication. I would like to return the user name they are logging in as.

The link from visakh16 is looking promising.

Thanks all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources