Get client OS username from SQL Server
<p>The requirement is to find which OS user logs in to a SQL Server database whenever the login is Windows Authentication or SQL Server authentication. Need to know <strong>WHO</strong> (AD account on the client PC) used which db account logged into SQL Server with what application. </p>

<p>The problem is I could not get the client's OS username when a SQL Server authentication user logs in. </p>

<p>For example, I used my AD account Domain name on a laptop， laptop123. Domain name is <code>Stackoverflow</code>, username is <code>developer1</code>, started Microsoft SQL Server Management Studio, connected with DB account (SQL Server authentication) <code>ERPAPPUSER</code>. I need to record the domain user <code>Stackoverflow/developer1</code>. </p>

<p>I can easily get the information that user <code>ERPAPPUSER</code> logged in from <code>laptop123</code> using Microsoft SQL Server Management Studio.</p>

<p>I created a database logon trigger and could get the client username, hostname, logon time, application name and so on, but could not get the client OS username.</p>

<pre><code>CREATE TRIGGER DB_ServerLogon
ON ALL SERVER 
WITH EXECUTE AS 'sa' 
FOR LOGON
AS
BEGIN
    INSERT INTO audit_history.dbo.db_logon_history
       SELECT 
           ORIGINAL_DB_NAME(), 
           ORIGINAL_LOGIN(),
           @@SPID,GETDATE(),
           HOST_NAME(),
           APP_NAME()
END
GO
</code></pre>

<p>Many thanks!</p>

<p>-- Added 'WITH EXECUTE AS 'as' ', otherwise for all users who don't have access to the db_logon_history will not be able to login.</p>