Recording Who Changed What: CreatedBy, UpdatedBy, and the Audit Log – Web API | Lesson 88

Published / updated8 Oct 2026

Introduction

Since Lesson 14, almost every SMS table has CreatedBy and UpdatedBy columns. Since Lesson 23, a trigger writes every change to a mark into AuditLogs, with a ChangedBy column. Until now, all of them were empty.

The API knows who is signed in (Lesson 85). This lesson passes that user all the way into SQL Server, so the database itself records who created and changed each row.

By the end of this lesson, you will be able to:

Explain why audit columns matter, and why they must not come from the request.
Read the current user anywhere in the API with IHttpContextAccessor.
Pass the user to SQL Server with SESSION_CONTEXT, on every connection.
Fill CreatedBy with a DEFAULT, and UpdatedBy in every UPDATE.
Prove that a pooled connection never carries one user's id to the next.

Why this matters

Marks, attendance, and payments are records that people argue about. "Who changed this?" must always have an answer, and the answer must be trustworthy.

An audit trail also protects staff. A teacher who corrected a mark properly can show exactly when and why, instead of being suspected of something she did not do.

The concept

Think of a school's visitor book. A book where visitors write their own names can be faked, but a gate that writes the name from your badge cannot. Here the badge is the token, and the gate is the database connection.

Example: three places that need the user

Where	Filled by	Lesson
`CreatedBy` on a new row	a `DEFAULT` that reads the session's user	88
`UpdatedBy` on a changed row	each `UPDATE` statement	88
`AuditLogs.ChangedBy`	`tr_Marks_Audit`, a trigger	23 (already reads it)

Example: SESSION_CONTEXT

SESSION_CONTEXT is a small set of key-value pairs that belongs to one database connection. The API sets a value once, and every statement, trigger, and default on that connection can read it:

Any connection
EXEC sys.sp_set_session_context @key = N'UserId', @value = 3, @read_only = 1;
SELECT CAST(SESSION_CONTEXT(N'UserId') AS INT);   -- 3

@read_only = 1 means nothing later on the same connection can change it. The trigger from Lesson 23 already has CAST(SESSION_CONTEXT(N'UserId') AS INT) with the comment "set by the API in Module 12". This is that lesson.

Example: why not take the user from the request?

A request could carry "updatedBy": 3 in its body, but the client chooses that value. Anyone could blame someone else. The user must come from the signed token and travel to the database without the client touching it.

Apply it to the SMS

Step 1: CreatedBy defaults

database/088-audit-user.sql
-- Lesson 88: who created each row, filled in by SQL Server itself.
-- The API tells each connection who the signed-in user is: sp_set_session_context @key = N'UserId'.
-- This script gives every CreatedBy column a DEFAULT that reads that value, so an INSERT that leaves
-- CreatedBy out records the user automatically. (tr_Marks_Audit, Lesson 23, already reads the same value
-- for AuditLogs.ChangedBy.) Without a user on the connection, the value is NULL, as before.
-- Safe to run more than once: columns that already have a default are skipped.
USE SchoolDb;
GO

DECLARE @Sql NVARCHAR(MAX) = N'';

SELECT @Sql += CONCAT(
    N'ALTER TABLE dbo.', QUOTENAME(t.name),
    N' ADD CONSTRAINT ', QUOTENAME(N'DF_' + t.name + N'_CreatedBy'),
    N' DEFAULT (CAST(SESSION_CONTEXT(N''UserId'') AS INT)) FOR CreatedBy;', NCHAR(10))
FROM sys.columns AS c
JOIN sys.tables AS t ON t.object_id = c.object_id
WHERE c.name = N'CreatedBy'
  AND t.schema_id = SCHEMA_ID(N'dbo')
  AND c.default_object_id = 0;

PRINT @Sql;
EXEC sys.sp_executesql @Sql;
GO

-- Check: every CreatedBy column and its default.
SELECT t.name AS TableName, dc.name AS DefaultName, dc.definition AS DefaultValue
FROM sys.columns AS c
JOIN sys.tables AS t ON t.object_id = c.object_id
LEFT JOIN sys.default_constraints AS dc ON dc.object_id = c.default_object_id
WHERE c.name = N'CreatedBy'
ORDER BY t.name;

-- Check: a connection that sets the user, then reads it back.
EXEC sys.sp_set_session_context @key = N'UserId', @value = 1;
SELECT CAST(SESSION_CONTEXT(N'UserId') AS INT) AS UserIdOnThisConnection;
GO

The script finds every CreatedBy column without a default and adds one, so it is safe to run twice. It gave 15 tables a default; the check at the end read back 1 after setting the session's user to 1.

Step 2: the current user

Core gets a one-property interface, ICurrentUser. The API answers it from the token:

src/SchoolApp.Api/Security/HttpCurrentUser.cs
using System.Security.Claims;
using SchoolApp.Core.Interfaces;

namespace SchoolApp.Api.Security;

// Lesson 88: the signed-in user of the current request, from the token's "sub" claim (Lesson 85).
// IHttpContextAccessor finds the current request from anywhere, so this class can be a singleton.
public class HttpCurrentUser : ICurrentUser
{
    private readonly IHttpContextAccessor _accessor;

    public HttpCurrentUser(IHttpContextAccessor accessor)
    {
        _accessor = accessor;
    }

    public int? UserId =>
        int.TryParse(_accessor.HttpContext?.User.FindFirstValue("sub"), out int id) ? id : null;
}

Program.cs (the Lesson 88 lines)
// Lesson 88: who is making each change. Every database connection reports this user to SQL Server.
builder.Services.AddHttpContextAccessor();
builder.Services.AddSingleton<ICurrentUser, HttpCurrentUser>();

IHttpContextAccessor finds the current request from anywhere, so HttpCurrentUser can be a singleton even though the user changes with every request.

Step 3: one hook on every connection

Every repository gets its connections from SqlConnectionFactory (Lesson 57). So that is the one place to change:

src/SchoolApp.Data/SqlConnectionFactory.cs
using System.Data;
using Microsoft.Data.SqlClient;
using SchoolApp.Core.Interfaces;

namespace SchoolApp.Data;

// Lesson 57: the one place that knows how to reach SchoolDb.
// Every repository asks this class for a new connection instead of building its own.
public class SqlConnectionFactory
{
    // The connection string from the SMS reference: the local default instance, Windows sign-in.
    public const string DefaultConnectionString =
        "Server=localhost;Database=SchoolDb;Trusted_Connection=True;TrustServerCertificate=True;";

    private readonly ICurrentUser? _currentUser;

    // Lesson 88: with a current user, every connection tells SQL Server who that user is, as soon as it opens.
    public SqlConnectionFactory(string connectionString, ICurrentUser? currentUser = null)
    {
        ConnectionString = connectionString;
        _currentUser = currentUser;
    }

    public string ConnectionString { get; }

    // Uses the SCHOOLDB_CONNECTION environment variable when it is set (for example, for LocalDB),
    // so the connection string can change without changing code.
    public static SqlConnectionFactory FromEnvironment()
    {
        string? fromEnvironment = Environment.GetEnvironmentVariable("SCHOOLDB_CONNECTION");
        return new SqlConnectionFactory(fromEnvironment ?? DefaultConnectionString);
    }

    // A new, closed connection. The caller opens it and disposes it (with using).
    // Lesson 88: whoever opens it (a repository, Dapper, a transaction), SetSessionUser runs first.
    public SqlConnection Create()
    {
        SqlConnection connection = new SqlConnection(ConnectionString);
        if (_currentUser is not null)
        {
            connection.StateChange += SetSessionUser;
        }
        return connection;
    }

    // SESSION_CONTEXT is a set of values that belongs to one connection. Triggers and DEFAULT constraints read
    // SESSION_CONTEXT(N'UserId') (database/088-audit-user.sql). read_only = 1: nothing later on this connection
    // can change it. A pooled connection is reset before it is reused, so the value never leaks to another user.
    private void SetSessionUser(object sender, StateChangeEventArgs e)
    {
        if (e.CurrentState != ConnectionState.Open || _currentUser?.UserId is not int userId)
        {
            return;
        }
        using SqlCommand command = ((SqlConnection)sender).CreateCommand();
        command.CommandText = "EXEC sys.sp_set_session_context @key = N'UserId', @value = @UserId, @read_only = 1;";
        command.Parameters.Add(new SqlParameter("@UserId", SqlDbType.Int) { Value = userId });
        command.ExecuteNonQuery();
    }
}

StateChange fires when a connection opens, whoever opens it: a repository, Dapper, or a transaction. The handler then sets the user with one short command. The console and the tests create the factory without a user, and nothing changes for them.

Step 4: inserts leave CreatedBy out; updates stamp UpdatedBy

A DEFAULT only applies when the INSERT does not mention the column. Passing CreatedBy = NULL would override it. So every Dapper INSERT dropped CreatedBy:

DapperMarksRepository.cs (the Lesson 88 SQL)
UPDATE dbo.Marks
SET MarksObtained = @MarksObtained, IsAbsent = @IsAbsent, Remarks = @Remarks,
    UpdatedAt = SYSUTCDATETIME(), UpdatedBy = CAST(SESSION_CONTEXT(N'UserId') AS INT)
WHERE Id = @Id;

INSERT INTO dbo.Marks (ExamSubjectId, EnrollmentId, MarksObtained, IsAbsent, Remarks)
VALUES (@ExamSubjectId, @EnrollmentId, @MarksObtained, @IsAbsent, @Remarks);   -- CreatedBy: the DEFAULT

The same change went into students, enrollments, guardians, attendance, fee payments, and invoice updates. The student insert also reads CreatedBy back with OUTPUT, so the new Student object knows its creator.

Step 5: Anitha corrects a mark

Signed in as Anitha (user 3), the Half Yearly Maths sheet for 10-A was entered, then Kabir's mark was corrected from 64 to 66. Signed in as the principal (user 2), a new student was admitted. Then SQL Server showed:

Output
Marks (paper 20, 10-A)
RollNumber MarksObtained CreatedBy UpdatedBy
1          78.00         3         NULL
2          91.00         3         NULL
3          66.00         3         3
4          88.00         3         NULL

AuditLogs
TableName RecordId Action OldValues                                   NewValues                                   ChangedBy
Marks     120067   UPDATE {"MarksObtained":64.00,"IsAbsent":false} {"MarksObtained":66.00,"IsAbsent":false} 3

Students
AdmissionNumber CreatedBy
ADM-2026-10001  2

The question from the hook has an answer: user 3, anitha.rao, changed Kabir's mark from 64 to 66. Reading a student still took about 3.5 ms, as in Lesson 81. All demo rows were removed afterwards.

Step 6: tests, including the pool

Connection pooling (Lesson 57) reuses connections. If a connection kept Anitha's id after she was done, the next request, maybe a parent's, would act under her name. SessionUserTests checks exactly that:

Show tests/SchoolApp.Tests/Integration/SessionUserTests.cs

tests/SchoolApp.Tests/Integration/SessionUserTests.cs
using Dapper;
using Microsoft.Data.SqlClient;
using SchoolApp.Core.Interfaces;
using SchoolApp.Data;

namespace SchoolApp.Tests.Integration;

// Lesson 88: every connection tells SQL Server who the user is, and a pooled connection never carries one
// user's id to the next.
[Trait("Category", "Integration")]
public class SessionUserTests
{
    private sealed class FixedUser : ICurrentUser
    {
        public FixedUser(int? userId)
        {
            UserId = userId;
        }

        public int? UserId { get; }
    }

    private static readonly string ConnectionString = SqlConnectionFactory.FromEnvironment().ConnectionString;

    private static async Task<int?> SessionUserAsync(SqlConnectionFactory factory)
    {
        await using SqlConnection connection = factory.Create();
        return await connection.ExecuteScalarAsync<int?>("SELECT CAST(SESSION_CONTEXT(N'UserId') AS INT);");
    }

    [Fact]
    public async Task ConnectionWithUser_ReportsTheUserToSqlServer()
    {
        Assert.Equal(42, await SessionUserAsync(new SqlConnectionFactory(ConnectionString, new FixedUser(42))));
    }

    [Fact]
    public async Task NextConnectionFromThePool_DoesNotKeepThePreviousUser()
    {
        // Same connection string, so the second open reuses the first one's pooled connection.
        await SessionUserAsync(new SqlConnectionFactory(ConnectionString, new FixedUser(42)));

        int? afterwards = await SessionUserAsync(new SqlConnectionFactory(ConnectionString));

        Assert.Null(afterwards);
    }

    [Fact]
    public async Task UserIdIsReadOnly_ForTheRestOfTheConnection()
    {
        await using SqlConnection connection = new SqlConnectionFactory(ConnectionString, new FixedUser(42)).Create();
        await connection.OpenAsync();

        SqlException error = await Assert.ThrowsAsync<SqlException>(() =>
            connection.ExecuteAsync("EXEC sys.sp_set_session_context @key = N'UserId', @value = 1;"));

        Assert.Equal(15664, error.Number);   // "The key has been set as read_only for this session."
    }
}

The second open reuses the pooled connection, and SESSION_CONTEXT comes back NULL: SQL Server resets a pooled connection before reusing it. The read-only test expects error 15664: "The key has been set as read_only for this session."

AuditUserTests signs in as the test teacher, takes attendance, enters and corrects marks, and checks that CreatedBy, UpdatedBy, and ChangedBy all hold his user id. The suite has 130 tests, passing three runs in a row.

Common mistakes

Taking "who" from the request. The client can name anyone. Fix: the signed token, carried to the database by the server.
Passing CreatedBy = NULL and expecting the default. An explicit NULL wins. Fix: leave the column out of the INSERT.
Setting the user only on some connections. Some rows get a creator, some do not. Fix: one hook in the connection factory.
A changeable session value. Later code on the connection could pretend to be someone else. Fix: @read_only = 1.
Auditing only in C#. Scripts, imports, and other apps bypass it. Fix: defaults and triggers in the database, which read the same session value.

In real projects

From 16 years of .NET projects

A school found that several published marks had been raised a week after the results. The audit table had every change, with old and new values, but ChangedBy was empty on every row.

The application had been connecting with one shared database login, and nothing ever told the database which person was behind each change.

We added the session-context step to the connection factory, as in this lesson. The next term, when a mark changed after publication, the office had a name and a time within a minute.

Best practices

Record the user from the signed token, never from the request body.
Set the user once per connection, in one place, and make it read-only.
Let database defaults and triggers fill audit columns, so every program is covered.
Leave CreatedBy out of inserts, and stamp UpdatedBy in every update.
Test that pooled connections never keep a previous user.

Recap

ICurrentUser gives the API's signed-in user to code outside controllers.
SqlConnectionFactory sets SESSION_CONTEXT(N'UserId') on every connection it opens.
CreatedBy defaults to that user; updates stamp UpdatedBy; the trigger fills ChangedBy.
Anitha's mark correction showed user 3 in Marks and AuditLogs.
Pooled connections are reset, so a user's id never reaches the next request: 130 tests pass.

Homework

Library module task: Add PUT /api/books/{isbn}/category to a single-file library API. Use the SMS's SqlConnectionFactory with your own ICurrentUser that reads the token's sub claim. The UPDATE must set Books.UpdatedBy from SESSION_CONTEXT, never from the request.

A correct result looks like:

Output
GET /api/books/9780143039655                    200
{"isbn":"9780143039655","title":"Malgudi Days","category":"Fiction","updatedAt":"2026-10-04T05:46:52","updatedBy":null}
PUT /api/books/9780143039655/category   {"category":"Short Stories"}   (librarian, sub = 1)   200
{"isbn":"9780143039655","title":"Malgudi Days","category":"Short Stories","updatedAt":"2026-10-08T12:21:07","updatedBy":1}

Show the solution

library-assignment/088-library-who-changed.cs
// Library homework, Lesson 88: record who changed a book. The librarian's id travels in the token as
// "sub"; the SMS's own SqlConnectionFactory reports it to SQL Server on every connection, and the UPDATE
// stamps Books.UpdatedBy from SESSION_CONTEXT(N'UserId'). (Run database/088-audit-user.sql first.)
// Run with:  $env:Library__SigningKey = "<at least 32 characters>";  dotnet run library-assignment/088-library-who-changed.cs
#:project ../src/SchoolApp.Data/SchoolApp.Data.csproj
#:sdk Microsoft.NET.Sdk.Web
#:package Microsoft.AspNetCore.Authentication.JwtBearer@10.0.11
#:property PublishAot=false
using System.Security.Claims;
using System.Text;
using Dapper;
using Microsoft.AspNetCore.Authentication.JwtBearer;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using Microsoft.IdentityModel.JsonWebTokens;
using Microsoft.IdentityModel.Tokens;
using SchoolApp.Core.Interfaces;
using SchoolApp.Data;

WebApplicationBuilder builder = WebApplication.CreateBuilder(args);
string signingKey = builder.Configuration["Library:SigningKey"] ?? "";
if (signingKey.Length < 32)
{
    throw new InvalidOperationException("Set Library__SigningKey to at least 32 characters.");
}
SymmetricSecurityKey key = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(signingKey));

builder.Services.AddControllers();
builder.Services.AddSingleton(key);
builder.Services.AddHttpContextAccessor();
builder.Services.AddSingleton<ICurrentUser, TokenUser>();
builder.Services.AddSingleton(provider => new SqlConnectionFactory(
    SqlConnectionFactory.FromEnvironment().ConnectionString, provider.GetRequiredService<ICurrentUser>()));
builder.Services.AddAuthentication(JwtBearerDefaults.AuthenticationScheme).AddJwtBearer(options =>
{
    options.MapInboundClaims = false;
    options.TokenValidationParameters = new TokenValidationParameters
    {
        ValidIssuer = "Library",
        ValidAudience = "Library",
        IssuerSigningKey = key,
        NameClaimType = "name"
    };
});
builder.Services.AddAuthorizationBuilder()
    .SetFallbackPolicy(new AuthorizationPolicyBuilder().RequireAuthenticatedUser().Build());

WebApplication app = builder.Build();
app.UseAuthentication();
app.UseAuthorization();
app.MapControllers();
app.Run("http://localhost:5088");

public record LoginRequest(string UserName, string Password);
public record CategoryRequest(string Category);

// The signed-in user's id, from the token's "sub" claim.
public class TokenUser : ICurrentUser
{
    private readonly IHttpContextAccessor _accessor;

    public TokenUser(IHttpContextAccessor accessor)
    {
        _accessor = accessor;
    }

    public int? UserId => int.TryParse(_accessor.HttpContext?.User.FindFirstValue("sub"), out int id) ? id : null;
}

public class BookRow
{
    public string Isbn { get; set; } = "";
    public string Title { get; set; } = "";
    public string Category { get; set; } = "";
    public DateTime? UpdatedAt { get; set; }
    public int? UpdatedBy { get; set; }
}

[ApiController]
[Route("api")]
public class LibraryController : ControllerBase
{
    private readonly SqlConnectionFactory _connections;
    private readonly SymmetricSecurityKey _key;

    public LibraryController(SqlConnectionFactory connections, SymmetricSecurityKey key)
    {
        _connections = connections;
        _key = key;
    }

    // One librarian, user id 1 (the SMS admin account). A real app checks a hashed password (Lesson 85).
    [HttpPost("login")]
    [AllowAnonymous]
    public IActionResult Login(LoginRequest request)
    {
        if (request.UserName != "librarian" || request.Password != "Library@2026")
        {
            return Unauthorized(new { message = "User name or password is wrong." });
        }
        string token = new JsonWebTokenHandler().CreateToken(new SecurityTokenDescriptor
        {
            Issuer = "Library",
            Audience = "Library",
            Subject = new ClaimsIdentity([new Claim("sub", "1"), new Claim("name", "librarian")]),
            Expires = DateTime.UtcNow.AddMinutes(30),
            SigningCredentials = new SigningCredentials(_key, SecurityAlgorithms.HmacSha256)
        });
        return Ok(new { accessToken = token });
    }

    [HttpGet("books/{isbn}")]
    public async Task<IActionResult> Get(string isbn)
    {
        await using SqlConnection connection = _connections.Create();
        BookRow? book = await connection.QuerySingleOrDefaultAsync<BookRow>(
            "SELECT Isbn, Title, Category, UpdatedAt, UpdatedBy FROM dbo.Books WHERE Isbn = @Isbn AND IsDeleted = 0;", new { Isbn = isbn });
        return book is null ? NotFound() : Ok(book);
    }

    // UpdatedBy comes from the connection's session, not from the request: the client cannot fake it.
    [HttpPut("books/{isbn}/category")]
    public async Task<IActionResult> SetCategory(string isbn, CategoryRequest request)
    {
        await using SqlConnection connection = _connections.Create();
        int rows = await connection.ExecuteAsync("""
            UPDATE dbo.Books
            SET Category = @Category, UpdatedAt = SYSUTCDATETIME(), UpdatedBy = CAST(SESSION_CONTEXT(N'UserId') AS INT)
            WHERE Isbn = @Isbn AND IsDeleted = 0;
            """, new { Isbn = isbn, request.Category });
        return rows == 0 ? NotFound() : await Get(isbn);
    }
}

After trying it, put the book back with UPDATE dbo.Books SET Category = 'Fiction', UpdatedAt = '2026-10-04 05:46:52', UpdatedBy = NULL WHERE Isbn = '9780143039655';.

Quiz

What is SESSION_CONTEXT in SQL Server?
1. A table
2. Key-value pairs that belong to one connection, readable by its statements, triggers, and defaults
3. A login
4. A transaction
Show answer
Answer: B. Key-value pairs that belong to one connection, readable by its statements, triggers, and defaults The API sets UserId once per connection.
Why did the Dapper inserts stop mentioning CreatedBy?
1. To save bytes
2. A DEFAULT only applies when the INSERT leaves the column out; an explicit NULL would win
3. CreatedBy was removed
4. Dapper cannot send it
Show answer
Answer: B. A DEFAULT only applies when the INSERT leaves the column out; an explicit NULL would win The default then fills it from the session's user.
Why is the user taken from the token, not from the request body?
1. The body is too small
2. The client chooses what is in the body; the signed token cannot be changed
3. Tokens are faster
4. Bodies are not allowed in PUT
Show answer
Answer: B. The client chooses what is in the body; the signed token cannot be changed An audit trail must not be fakeable by the client.
What does @read_only = 1 do?
1. Makes the table read-only
2. Stops anything later on the same connection from changing the value
3. Makes the connection read-only
4. Hides the value
Show answer
Answer: B. Stops anything later on the same connection from changing the value Changing it again gives error 15664.
What happens to the session's UserId when a pooled connection is reused?
1. It stays
2. SQL Server resets it, so the next user starts with NULL
3. It becomes 0
4. The pool refuses the connection
Show answer
Answer: B. SQL Server resets it, so the next user starts with NULL SessionUserTests proves it.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: Security interview questions.

Fresher (0-2 yrs)

F What are audit columns, and why are they useful? concept

Columns such as CreatedAt, CreatedBy, UpdatedAt, and UpdatedBy that record when and by whom a row was created and last changed. They answer "who changed this?" for disputed data such as marks and payments, and they protect staff who made correct changes.

See the example · Permalink

F Why must the 'changed by' user come from the token and not from the request body? concept

The client decides what is in the body, so anyone could write someone else's id. The signed token cannot be changed, and the server carries the user from it into the database without the client touching it.

See the example · Permalink

F What is SESSION_CONTEXT in SQL Server? concept

A set of key-value pairs that belongs to one connection, set with sp_set_session_context and read with SESSION_CONTEXT(N'key'). Statements, triggers, and DEFAULT constraints on that connection can read it, which lets the API tell the database who the user is.

See the example · Permalink

Mid (3-5 yrs)

M A column has a DEFAULT, but new rows still get NULL. Why? scenario

The INSERT names the column and passes NULL, and an explicit value, even NULL, wins over the default. In the SMS, the Dapper inserts leave CreatedBy out, so DF_..._CreatedBy fills it from the session's user.

See the example · Permalink

M How can a singleton service know the current request's user in ASP.NET Core? coding

Inject IHttpContextAccessor (registered with AddHttpContextAccessor). It finds the current request's HttpContext from anywhere, so a singleton such as the SMS's HttpCurrentUser can read the user's "sub" claim each time it is asked, even though the user changes with every request.

See the example · Permalink

M Why set the session user in the connection factory instead of in each repository? design

Every connection comes from the factory, so one hook covers every repository, Dapper call, and transaction, and nobody can forget it. The SMS adds a StateChange handler that runs sp_set_session_context with @read_only = 1 as soon as a connection opens.

See the example · Permalink

Senior (6-9 yrs)

S With connection pooling, could one user's id stay on a connection and be used by the next request? scenario

Not with SESSION_CONTEXT: SQL Server resets a pooled connection's session state before it is reused, so the next user starts with NULL. The SMS proves it with a test that opens a connection with user 42, then opens another from the same pool without a user and reads NULL. @read_only = 1 also stops code on the same connection from changing the id.

How to answer: Explain pooling and the reset, show how you would prove it with a test, and mention read-only values.

See the example · Permalink

S Marks were changed after results were published, and the audit log has old and new values but no user. How do you fix this for the future? scenario

The application connects with one shared login, and nothing tells the database who the person is. Pass the signed-in user to every connection (SESSION_CONTEXT), let the triggers and defaults read it, and stamp UpdatedBy in updates. Add a test that a change through the API records the user, and consider blocking changes to published results without a recorded reason.

How to answer: Explain why the user was missing, describe the end-to-end fix, how to verify it, and a process rule for sensitive changes.

See the example · Permalink

Architect / Lead (10+ yrs)

A How would you design auditing for every important table in a large system? design

Decide what must be audited (who, when, what changed, from where), then choose a mechanism: triggers that write before-and-after values (as tr_Marks_Audit does), SQL Server temporal tables for full history, or change data capture feeding a separate audit store. Pass the user and request id on every connection, protect the audit data from changes (separate permissions, append-only), set retention rules, and give the office a way to search it.

How to answer: Compare the mechanisms by detail, cost, and tamper resistance, and cover identity propagation, retention, and how people will use the audit data.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-88-who-changed-what (code repository coming soon)
Files: database/088-audit-user.sql, src/SchoolApp.Data/SqlConnectionFactory.cs, src/SchoolApp.Api/Security/HttpCurrentUser.cs, tests/SchoolApp.Tests/Integration/SessionUserTests.cs, tests/SchoolApp.Tests/Api/AuditUserTests.cs, library-assignment/088-library-who-changed.cs
Video: coming soon

What's next

Security is in place. Next, filters: small pieces of code that run around controller actions, for jobs many actions share, such as a read-only switch for year-end closing. Lesson 89: Filters

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.

Introduction​

Why this matters​

The concept​

Example: three places that need the user​

Example: SESSION_CONTEXT​

Example: why not take the user from the request?​

Apply it to the SMS​

Step 1: CreatedBy defaults​

Step 2: the current user​

Step 3: one hook on every connection​

Step 4: inserts leave CreatedBy out; updates stamp UpdatedBy​

Step 5: Anitha corrects a mark​

Step 6: tests, including the pool​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: three places that need the user

Example: SESSION_CONTEXT

Example: why not take the user from the request?

Apply it to the SMS

Step 1: CreatedBy defaults

Step 2: the current user

Step 3: one hook on every connection

Step 4: inserts leave CreatedBy out; updates stamp UpdatedBy

Step 5: Anitha corrects a mark

Step 6: tests, including the pool

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next