Windows Account Information from a SQL Login

Question

Windows Account Information from a SQL Login

Jon-183221

SSCommitted

Points: 1908
More actions
March 1, 2006 at 3:24 am

#103104

Hi All,
This is probably a really dumb question but I'm in a situation where if its possible it will stop me from having to re-write loads of code so I'm prepared to look dumb.
Is it possible to get windows account information back from a SQL Login? Our application uses an application login to access SQL Server, but for one particular area I need to know some information about the windows user. The information I'm after would be the nt_domain and nt_username.
I know that I could get the users to login into SQL using Windows Authentication but this will open a whole different can of worms.
Any help would be really appreciated even if its to tell me that I'm just dumb.
TIA
Jon

Viewing 7 posts - 1 through 6 (of 6 total)

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

The application knows about the Windows user. However, if you connect to SQL Server using a SQL Server login, no Windows information is passed. Therefore, the SQL Server would have no knowledge of the actual Windows login. You can get around this if you have stored procedures that need to know the login info by passing that in as parameters, but you can see the potential security issues that can result if someone were able to execute the stored procedure outside of your control.

K. Brian Kelley
@kbriankelley

Jon-183221 SSCommitted Points: 1908 More actions · Answer 2

Thanks for your response. Thats what I thought, but thank you for clarifying that with me.

Regards

Jon

Jen-169507 Right there with Babe Points: 776 More actions · Answer 3

actually... if you are doing some audit...

you can check if sysprocesses provides you with enough information you need...

HTH

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 4

Or, if you can correlate application user to ads user, you can get info via a stored procedure from ads.

(i'm assuming the user is named somehow in the application)

regards karl

Best regards
karl

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

Not if the login to SQL Server is done using a SQL Server login. That's the issue at hand. When a SQL Server login is used, no Windows account information is passed to SQL Server.

K. Brian Kelley
@kbriankelley

Jen-169507 Right there with Babe Points: 776 More actions · Answer 6

i assume this is development, in audit it is not only the windows account that tells you who did what, you can also retrieve information like ip addresses,domain, time, what is being done/issued, etc...

--if the information you need is not directly available look for workarounds,

sometimes, the answer is really not obvious so you need to put out extra effort to get what you want