SQL Server Performance

How to get Windows login name?

Discussion in 'SQL Server 2005 General DBA Questions' started by pcsql, Mar 8, 2007.

  1. pcsql New Member

    How to get the Windows login name inside SQL Server 2005 when the SQL Server 2005 is accessed thru terminal service? Cannot use .Net.
  2. MohammedU New Member

    SQL server management studio (SSMS)...

    Open the SSMS connect to the database engine and expand... expand SECURITY folder...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  3. pcsql New Member

    Hi MohammedU,

    I forget to include the most important information:

    SQL Server 2005 is connected using SQL Authentication.
  4. Adriaan New Member

    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.
  5. Adriaan New Member

    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.
  6. pcsql New Member

    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.


  7. Adriaan New Member

    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.
  8. MohammedU New Member

    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
  9. pcsql New Member

    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?
  10. khtan New Member

    Not when you are using SQL Authentication. But these information should be easily available from your application. Why not do it there.


    KH
  11. MohammedU New Member

    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
  12. pcsql New Member

    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.


  13. MohammedU New Member

    Yes, it is always possible...you can call OS commands with xp-cmdshell command...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  14. Adriaan New Member

    I wouldn't bother taking the difficult road here. Let the client app feed the Windows login to SQL Server, or use Windows authentication.
  15. MohammedU New Member

    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
  16. pcsql New Member

    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.
  17. MohammedU New Member

    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

Share This Page