How to track username for audit trail in sql server

Guest User

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vishal Joshi2y ago
Accepted 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
+1

Jayraj Chhaya2y ago

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.