How to get Windows login name? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to get Windows login name?

How to get the Windows login name inside SQL Server 2005 when the SQL Server 2005 is accessed thru terminal service? Cannot use .Net.
SQL server management studio (SSMS)… Open the SSMS connect to the database engine and expand… expand SECURITY folder…
MohammedU.
Moderator
SQL-Server-Performance.com
Hi MohammedU, I forget to include the most important information: SQL Server 2005 is connected using SQL Authentication.
In SQL 2000 we had SYSTEM_USER() which reflected the domain name when logged in under Windows authentication, and the SQL login when logged in under SQL Server authentication.
It also depends how people are logging on to the terminal server. That would be the login that SQL Server can see – it cannot look beyond the session in which the client app is running.
Hi Adriaan, Thanks for the reply. I’m aware of the SYSTEM_USER(). However, in my case, I need to find the windows login when the authentication is SQL Authentication.
You can’t ask SQL Server to ignore Windows authentication and still know about the Windows credentials. You can’t have your cake and eat it, as the saying goes.
quote:Originally posted by pcsql Hi Adriaan, Thanks for the reply. I’m aware of the SYSTEM_USER(). However, in my case, I need to find the windows login when the authentication is SQL Authentication.

What kind of Windows loign you want to know? when you logged in with SQL authentication… Can you provide an example?
MohammedU.
Moderator
SQL-Server-Performance.com
Hi MohammedU, For example, 2 users are logged into a terminal server installed with SQL Server 2005 using different windows login name. Both of them connect to the SQL Server 2005 using SQL Authentication. Is there a way in SQL Server 2005 to find the windows login names?
Not when you are using SQL Authentication. But these information should be easily available from your application. Why not do it there.
KH
I don’t think there is anything within the sql which will provide that information…
But you can check windows security log or any other windows commonds…
MohammedU.
Moderator
SQL-Server-Performance.com
Hi MohammedU, I guess this can be done if using .Net Class Library. May be it can be done by using extended stored procedure calling OS functions.
Yes, it is always possible…you can call OS commands with xp-cmdshell command…
MohammedU.
Moderator
SQL-Server-Performance.com
I wouldn’t bother taking the difficult road here. Let the client app feed the Windows login to SQL Server, or use Windows authentication.
I agree with Adriaan and first of all…
Can you give us the reason what you want to accomplish with this? MohammedU.
Moderator
SQL-Server-Performance.com
Hi MohammedU, An application is using SQL Server Authentication to connect SQL Server 2000/2005. The application is installed on a machine with SQL Server 2000/2005 and terminal service. Users log on to the machine thru Remote Desktop connection and then run the application. Everything is fine except there is no way that I can tell who is running since only one SQL Server login is used for everyone. So, I want to find out how to get the Windows Login name since it is unique.
You can try reading the regitry but you need to have SA rights to read the registry with xp-cmdshell ….
MohammedU.
Moderator
SQL-Server-Performance.com
]]>