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.

Vishal Joshi · Answer

Hello You can use SUSER_SNAME() to get the current username instead of dbo. this is used if you want to get the username by SID so SUSER_SNAME can be invoked with a parameter like SUSER_SNAME([server_user_sid]) but the SID is optional if you don’t pass that parameter the current user is returned. you can set the current user name like below. SET @CurrentUser = SUSER_SNAME(); -- Get the current user Thanks Vishal Joshi

Jayraj Chhaya · Answer

To track the real username for user deletion in an audit trail you can modify the provided stored procedure [dbo].[spGetVisitorsTripSheetAuditReport]. Currently the procedure uses the @CurrentUser variable to display the real identity if the Username is null or 'dbo'. However it does not capture the real username for deletions. To achieve this you can modify the CASE statement in the SELECT clause of the query. Instead of using @CurrentUser for deletions you can use the system function SUSER_SNAME() to retrieve the real username. Here's an updated version of the query: SELECT DISTINCT VTA.Tripsheetno VTA.Horse VTA.Trailer VTA.[Security] AS Username 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 SUSER_SNAME() -- Show real identity if Username is null WHEN VTA.Username = 'dbo' THEN SUSER_SNAME() -- 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)) By using SUSER_SNAME() instead of @CurrentUser the query will now display the real username for deletions in the audit trail.