• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

Get Windows account name of user who's running query

There is a function in SQL Server 2005 that returns the WINDOWS login of the person running a query.
It's not System_user or Current_User or User_Name.

For Windows users connecting to SQL Server with a SQL account, those return the SQL login.

But this application always runs under the account ProgramUser (for example).  But I need to audit whether 'JDoe', 'SSmith' or 'MBrown' is the windows login behind it.

I've used this before but I can't find it today ...

What's the function I'm looking for!

Thanks!
0
Daniel Wilson
Asked:
Daniel Wilson
  • 5
  • 3
1 Solution
 
chapmandewCommented:
SUSER_SNAME()
0
 
Daniel WilsonAuthor Commented:
No, I'm getting the same thing.

Other ideas?
0
 
chapmandewCommented:
what is your login?  what are you wanting to see?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
chapmandewCommented:
so, for a sql login, you want to see the MachineName that the connection is made, right?
0
 
TunerMLSystems EngineerCommented:
suser_sname() was what I thought you were after as well but try original_login()
0
 
Daniel WilsonAuthor Commented:
Most of the users are connecting to Citrix_Server to run the application.  They are logging into Windows as 'JDoe', 'SSmith', 'MBrown', etc.

But they are all running the application as 'ProgramUser'.

I am connecting to Remote Desktop on DB_Server as 'ApplicationAdmin' and logging into SQL Server via SSMS as 'ProgramManager'.

I want to see the Windows login --  'JDoe', 'SSmith', 'MBrown', 'ApplicationAdmin' -- NOT 'ProgramUser' or 'ProgramManager'.

Thanks!
0
 
chapmandewCommented:
But, SQL has no idea that those windows logins exist (it doesn't even care..that is why you can use SQL logins from linux machines, etc).  You can look at the HOST_NAME() to see what machine they are connecting from, but you can't determine their windows account, because SQL doesn't know it.
0
 
chapmandewCommented:
no, original_login() only matters where there are context switches, such as using EXECUTE AS
0
 
Daniel WilsonAuthor Commented:
Looks like you're right.  It's when using Windows authentication that those data are available.

Thanks!
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now