Post reply

get ip from username

  • November 16, 2010 at 4:57 am

    #231343

    hi all.

    how to get ip address from username by xp_cmdshell?

    what comand of xp_cmdshell for this purpose?

  • November 16, 2010 at 5:17 am

    #1251237

    are you looking to get the IP address of the client connecting to SQL Server?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • November 16, 2010 at 6:20 am

    #1251277

    A netstat command?

    By needing to use xp_cmdshell you are asking to run an o/s command to get the info. Not really anything to do with sql server.


    Cursors never.
    DTS - only when needed and never to control.

  • November 16, 2010 at 6:42 am

    #1251297

    in 2005 and above, there is a DMV that has the IP address, but note it's possible the value ls '<localhost>' or null if they connected via shared pipes.

    --the auditing snippet below works fine in a

    --login trigger,

    --database trigger

    --or any stored procedure.

    SELECT

    getdate() AS EventDate,

    DB_NAME() AS DBName,

    HOST_NAME() AS HostName,

    APP_NAME() AS ApplicationName,

    OBJECT_NAME(@@PROCID) AS ProcedureName,

    USER_ID() AS Userid,

    USER_NAME() AS UserName,

    SUSER_ID() AS sUserid,

    SUSER_SNAME() AS sUserName,

    IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],

    IS_MEMBER('db_owner') AS [Is_DB_owner],

    IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],

    IS_MEMBER('db_datareader') AS [Is_DB_Datareader],

    ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],

    client_net_address AS ipaddress,

    auth_scheme AS AuthenticationType

    FROM sys.dm_exec_connections where session_id = @@spid

    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!

  • November 16, 2010 at 7:49 am

    #1251362

    I would also add the auth_scheme column to Lowell's query to check for NTLM or Kerberos authentication type.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

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

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