I have these two stored procedures, and Sales.spExec1 calls dbo.spExec2 .

When I try to REVERT in the 2nd stored procedure, it doesn't go back to original_login() .

Does this mean that REVERT works within a stored procedure? It does revert when I call it in Sales.spExec1 .

CREATE PROCEDURE Sales.spExec1 
AS
BEGIN
    PRINT SUSER_SNAME()  -- 'MyDomain\Bob.Smith'
    PRINT original_login() -- 'MyDomain\Bob.Smith'

    EXECUTE AS USER = 'TempUser';
    EXEC dbo.spExec2

    REVERT;

    /* REVERT works in SP where initial EXECUTE AS USER ran */
    print SUSER_SNAME()  -- 'MyDomain\Bob.Smith'
    print original_login() -- 'MyDomain\Bob.Smith'
END;
GO

CREATE PROCEDURE dbo.spExec2
AS
BEGIN
    PRINT SUSER_SNAME()  -- 'TempUser'
    PRINT original_login() -- 'MyDomain\Bob.Smith'

    REVERT;

    /* REVERT did not work */
    print SUSER_SNAME()   -- Still `TempUser'
    print original_login()
END;
GO