Post reply

How to determine Windows domain username when the user connected to SQL as SQL login with SA access

  • March 9, 2017 at 1:14 pm

    #1932604

    When connecting using a SQL Server authenticated account, you won't have the user's domain username, but you can still determine their host name, client ip address, and some other useful information from dm_exec_connections and dm_exec_sessions. You could also put this into a login trigger and capture to a table.

    SELECT c.session_id, c.auth_scheme, s.login_name, s.original_login_name
     , c.connect_time, c.client_net_address, s.host_name, s.program_name
    FROM sys.dm_exec_connections AS c
    LEFT JOIN sys.dm_exec_sessions AS s ON s.session_id = c.session_id
    WHERE c.session_id = @@SPID;

    52    SQL    sa    SA    2017-03-09 13:02:12.811    111.222.333.444    DHS93932    Microsoft SQL Server Management Studio - Query

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • March 9, 2017 at 1:29 pm

    #1932608

    Also, if you have an application or user who insists on connecting as "SA", then one option is to rename the account and then recreate it with lesser privileges.

    So long as you grant them the least privileges they need for their day to day job, 9 out of 10 users won't know their vanity login isn't really a member of SYSADMIN role.

    alter login [SA] DISABLE;
    alter login [SA] with name = [SA_BAK];

    create login [SA] with PASSWORD = 'wh@t3v3r1210', DEFAULT_DATABASE = master;

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 2 posts - 16 through 16 (of 16 total)

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