How can I track the flow of a change caused by a stored procedure in Microsoft SQL Server?

With the following table:

CREATE TABLE [Test]
(
    [Id] BIGINT IDENTITY NOT NULL,
    [Name] CHARACTER VARYING(255) NOT NULL,
    [Description] CHARACTER VARYING(MAX) NOT NULL,

    PRIMARY KEY([Id])
);

I control creation of new records with the stored procedure:

CREATE PROCEDURE [Test_Create]
    @SessionId BINARY(32),
    @Name CHARACTER VARYING(255),
    @Description CHARACTER VARYING(MAX)
AS
BEGIN
    BEGIN TRANSACTION;
        BEGIN TRY
            INSERT INTO [Test] ([Name], [Description])
            VALUES (@Name, @Description);
        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION;
            THROW;
        END CATCH
    COMMIT TRANSACTION;
END

I have a trigger that simply logs the before and after values for this table, for all changes. When my stored procedure is called, this trigger is fired and logs to another table. However, my trigger obviously cannot have the @SessionId variable to log which website user session caused the stored procedure to be invoked by the SQL client running on a web server. I do not want to use SET CONTEXT_INFO because it is unstable with connection pooling and parallel execution plans.

That said, I thought I could correlate the @SessionId passed in to the stored procedure with the @@SPID value, but again this is a little tricky with connection pooling. It won't work, as far as I can tell.

How can I track which user on my website, assuming I have a unique session number for them passed into my stored procedure, caused specific changes to any of my tables?

If they invoke a stored procedure, I need to know the stored procedure invoked and how it altered the tables (before and after values).

Is there perhaps an alternative to retrieve the following information and link them together to form an audit trail?

The website user/session that caused a stored procedure to be invoked
The stored procedure that was invoked and its parameters
The resultant data queries
The effects of the queries (before and after data)

Solution

I would recommend that you modify your table structure so that the @SessionId value is stored in the table for creation and modification.

I have an ASP.Net app that also must log all changes for auditing purposes, and the tables that require auditing all have

CreatedDateTime DateTime NOT NULL
CreatedBy nvarchar(50) NOT NULL
ModifiedDateTime DateTime NULL
ModifiedBy nvarchar(50) NULL

columns

Users log in with Windows Authentication, and we pass the user's domain\username to all the stored procs so that it can be stored in the CreatedBy or ModifiedBy col. The CreatedDateTime/ModifiedDateTime values are set by the stored procs.

Our change log auditing is done by a CLR trigger which just extracts the ModifiedBy value on the new record and this is logged as the person making the change.

Using your @SessionId value would work just as well I would think.