get OS username

Question

get OS username

shen-dest

SSCrazy

Points: 2498
More actions
May 14, 2010 at 5:52 am

#221571

Hi,
For a SQL server authenticated connection to SQL Server 2008, is it possible to get the OS username from the terminal where connection is made. thanks.
Regards,
MShenel

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

as far as i know, you can get it only if they used their windows credentials to login to the server; if they logged in with a SQL login, it's not available...even if you went to xp_cmdShell and ran "whoami", it will not return the windows user.

select

user_name() AS [user_name],

suser_name() AS [suser_name],

current_user AS [current_user],

system_user AS [system_user],

session_user AS [session_user],

user AS

/*

user_name suser_name current_user system_user session_user user

dbo sa dbo sa dbo dbo

dbo DISNEY\lizaguirre dbo DISNEY\lizaguirre dbo dbo

guest bob guest bob guest guest

*/

you can also get their IP and hostname, which can help:

select host_name(),app_name(),client_net_address from sys.dm_exec_connections where session_id = @@spid

/*

(No column name)(No column name)client_net_address

D223Microsoft SQL Server Management Studio - Query192.168.0.40

*/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

shen-dest SSCrazy Points: 2498 More actions · Answer 2

Thanks we already get hostname,appname for specific operations.We DBAs do administrative task on a terminal server with a SQL server generic user. If we cant get osuser somehow then you we must have seperate logins for each DBA for auditing.

Regards,
MShenel

Rome1981 SSC-Addicted Points: 445 More actions · Answer 3

What if you enabled auditing by machine? Then you could capture the machine and correlate it back.