How to track username for audit trail in sql server

Question

// store proc
CREATE PROCEDURE [dbo].[spGetVisitorsTripSheetAuditReport] '2024-04-10', '2024-05-13'
(
    @StartDate  DATETIME,
    @EndDate    DATETIME
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @CurrentUser NVARCHAR(100);
    SET @CurrentUser = USER_NAME(); -- Get the current user

    SELECT DISTINCT
        VTA.Tripsheetno,
        VTA.Horse,
        VTA.Trailer,
        VTA.[Security] AS Username, -- Alias for clarity
        VTA.Driver,
        VTA.Station,
        ISNULL(VWTA.totalweight, VTA.TotalEntryWeight) AS TotalEntryWeight,
        ISNULL(VWTA.totalweight, VTA.TotalLeaveWeight) AS TotalLeaveWeight,
        VTA.DCheckWeight,
        VTA.VisTripdate,
        VTA.Tracktrip,
        VTA.DcheckTime,
        VTA.DCFullweight,
        VTA.UpdateDate,
        CASE 
            WHEN VTA.Operation = 'I' THEN 'Insert'
            WHEN VTA.Operation = 'U' THEN 'Update'
            ELSE 'Delete'
        END AS Operation,
        CASE 
            WHEN VTA.Username IS NULL THEN @CurrentUser -- Show real identity if Username is null
            WHEN VTA.Username = 'dbo' THEN @CurrentUser -- Show real identity if Username is 'dbo'
            ELSE VTA.Username
        END AS Username,
        VWTA.Customer,
        VWTA.Product,
        VWTA.totalWeight AS TotalWeight,
        VWTA.Purpose_code
    FROM 
        [dbo].[VisitorTripsheetAudit] VTA
    LEFT JOIN 
        [dbo].[VisitorWeighbridgeTicketAudit] VWTA ON VTA.Tripsheetno = VWTA.Trip
    WHERE 
        VTA.UpdateDate BETWEEN @StartDate AND DATEADD(SECOND, -1, DATEADD(DAY, 1, @EndDate)) -- Adjusted for end of the day
END


//output from the query
2024-04-19 10:16:57.257 Insert  Rebaone
2024-04-19 10:29:05.067 Delete  dbo // need to find out who not this real username for deletion
2024-04-19 10:20:24.750 Insert  Rebaone
2024-04-19 10:29:13.737 Delete  dbo

Hi Team

I need to get an audit trail for user deletion, meaning real username instead of dbo, must give me real username. How can I achieve this on my query, please assist.