CODEWITHSUNDEEP
sql-servertransactions
jameswilddev
jameswilddev

Reputation: 612

EXECUTE AS/REVERT within a transaction in SQL Server

Some work being done with SQL Server requires that I switch which user we are executing as during a transaction from C# side. I am wondering how transactions and context switches interact in SQL Server.

(pseudocode)

using(var transaction = database.GetTransaction())
{
    using(database.ExecuteAs("User A")) // EXECUTE AS User = "User A"
    {
        // Run queries as User A as part of the transaction
    } // REVERT
    using(database.ExecuteAs("User B")) // EXECUTE AS User = "User B"
    {
        // Run queries as User B as part of the transaction
    } // REVERT
    // Run queries as DBO as part of the transaction
    transaction.Commit();
}

I've looked at the documentation for EXECUTE AS and can't see any notes on whether transactions are honoured after switching user. Does anyone know if they are?

Upvotes: 0

Views: 401

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46233

Database transactions (SqlTransaction) are session-scoped rather than user-scoped so you can use EXECUTE AS and REVERT within the same transaction (and database connection).

Upvotes: 1

Related Questions