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 WHO (AD account on the client PC) used which db account logged into SQL Server with what application.

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

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

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

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.

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

Many thanks!

-- 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.