How to get Windows login Name

  • Hi

    How can i find the Windows login name connected to the system?

    Environment: Users are connecting the Terminal Server with their Windows logins (remote logins)

    and opens a program with their SQL logins or SA.

    i need the windows login name.

    Thanks.

    cemuney

  • No way, AFAIK.

    I had to do something similar in the past and I ended up changing the program name property in the connection string to add the connected windows login. Is this a possible strategy in your scenario?

    -- Gianluca Sartori

  • No way.

    i cannot change the programs connection string.

    thanks anyway.

  • hi ,

    why dont you use a ddl , database for events trigger , it will capture the app login and domain user

    Regards

    Jody

  • Thanks but

    thats the reason that i need WindowsNt\LoginName, i am also using DDL trigger.

    INSERT INTO dbo.tbDropTableLog(EventTime,EventType,ServerName,DatabaseName,ObjectType,ObjectName,LoginName,UserName,HostName,CommandText)

    SELECT

    REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')),

    'T', ' '),

    CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')),

    CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ServerName)')),

    CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/DatabaseName)')),

    CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')),

    CONVERT(VARCHAR(40), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')),

    CONVERT(VARCHAR(100),@xmlEventData.query('data(/EVENT_INSTANCE/LoginName)')),

    CONVERT(VARCHAR(30), @xmlEventData.query('data(/EVENT_INSTANCE/UserName)')),

    CONVERT(VARCHAR(50), (select hostname from sys.sysprocesses where spid =@@SPID) ),

    CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))

    EVENT_INSTANCE/LoginName

    EVENT_INSTANCE/UserName

    But WindowsNtloginname doses not exists in this event.

    cemuney

  • http://www.sqlservercentral.com/Forums/Topic1126293-391-1.aspx#bm1126863

    this has been discussed in the above post as well. Since the users are connecting via terminal services and using sql logins theres not much you can really do.

    Jayanth Kurup[/url]

  • User connect the Terminal server first with the windows login and then connect the SQL server with SA how its possible ,it will show SA because users connectiing the SQL server with SA not windows Login

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • Yes that is the question!

    SQL server can catch the lots of values like HOSTNAME even if logins with SA.

    (SA is just an example.

    Ofcourse they have their own SQL Server logins and passwords. (Sql Authentication)

    The aim is to catch the REMOTE Server login name for auditing.

    User can connect the Terminal Server and open SSMStudio and delete something with SA password.)

    thanks.

  • Cem Uney (6/27/2011)


    Hi

    How can i find the Windows login name connected to the system?

    Environment: Users are connecting the Terminal Server with their Windows logins (remote logins)

    and opens a program with their SQL logins or SA.

    i need the windows login name.

    Thanks.

    cemuney

    Have you considered turning the SPROC into a CLR assembly and getting it via C# or VB.NET?

    What about using WMI via VBS and passing to the SPROC or vice-versa via temp .TXT file or table?

    Just a thought but I'm pretty sure one of those creative approaches might help, if I understand you....

    Best,

    Rene

Viewing 9 posts - 1 through 8 (of 8 total)

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