Post reply

Is there away to Know who is the NT USer when the login is sql server login

  • June 8, 2010 at 1:15 am

    #223562

    I have some Sql server logins (Sql Server authentication) and I need to Know which Active Directory Login Used this Login

    The database is logging the system_user when certian acctions are maid on certian tables (mainly for tracking)

    beacause all most all users are NT LOGINS

    now I nead to create 2 sql server logins to be used by several users but I need to Know wich is wich

    I can Not Find It in the dynamic vies nor the sys processes table

  • June 8, 2010 at 3:57 am

    #1177366

    Try this:

    select sys.dm_exec_sessions.session_id,sys.dm_exec_sessions.host_name,sys.dm_exec_sessions.program_name,

    sys.dm_exec_sessions.client_interface_name,sys.dm_exec_sessions.login_name,

    sys.dm_exec_sessions.nt_domain,sys.dm_exec_sessions.nt_user_name

    ,sys.dm_exec_connections.client_net_address,

    sys.dm_exec_connections.local_net_address,sys.dm_exec_connections.connection_id,sys.dm_exec_connections.parent_connection_id,

    sys.dm_exec_connections.most_recent_sql_handle,

    (select text from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as sqlscript,

    (select db_name(dbid) from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as databasename,

    (select object_id(objectid) from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as objectname

    from sys.dm_exec_sessions inner join sys.dm_exec_connections

    on sys.dm_exec_connections.session_id=sys.dm_exec_sessions.session_id

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • June 8, 2010 at 4:18 am

    #1177371

    thanks for the query

    but still it i am not getting the result I want

    the NT Login Name and and NT Domian ARE empty for sql Server Logins

  • June 8, 2010 at 5:44 am

    #1177398

    Gil i don't believe it is possible.

    I'd seen this question a couple of times before, and here are some threads on the same issue.

    http://qa.sqlservercentral.com/Forums/Topic921933-391-1.aspx

    http://qa.sqlservercentral.com/Forums/Topic906015-145-1.aspx?Highlight=whoami

    http://qa.sqlservercentral.com/Forums/Topic905937-146-2.aspx

    i had tried reading the registry of HKCurrentUser with xp_cmdshell, but it trolls a copy of the default user (because the user context ends up being the service that SQl is running under, ie NT Network Authority or system) instead of the current user.

    the PATH variables do not hold the same values when harvested from xp_cmdshell either, so you can't fidn the current user's directory and infer it, nor does whoami work either.

    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!

  • June 8, 2010 at 8:24 am

    #1177500

    No.

    When the connection is made to SQL Server, based on the way the login packet is structured, only the SQL Server login information is passed. There's no support for anything else. After all, there's no guarantee that a Windows-based system is the client.

    Therefore, the only way you'd know who the actual Windows user was on the client side is if the application passed the information via a query.

    K. Brian Kelley
    @kbriankelley

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

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