SQL Server user name functions

Consider this T-SQL:

CREATE USER my_test_user WITHOUT LOGIN;
SELECT USER_NAME(), USER, SUSER_SNAME(),SYSTEM_USER, suser_name();
EXECUTE AS USER = 'my_test_user' WITH NO REVERT;
SELECT USER_NAME(), USER, SUSER_SNAME(),SYSTEM_USER, suser_name();

I'm looking at using these accounts WITHOUT LOGIN for auditing purposes. Basically, my "get a database connection" code returns a connection on which the EXECUTE AS USER...WITH NO REVERT has already been executed.

The problem is that I can't get consistent results from any of these user name functions. The two lines of output are:

dbo           dbo           original_user      original_user     original_user
my_test_user  my_test_user  S-1-9-3-XXXXX..    S-1-9-3-XXXXX..   S-1-9-3-XXXXX..

The USER functions produce correct output AFTER the 'EXECUTE AS', but beforehand they're showing dbo rather than the user name The SUSER functions are just the opposite -- they're correct initially but after impersonation they're showing some sort of ID

The MSDN docs for SUSER_SNAME explicitly give an example where this is supposed to work.

UPDATE: What I'm looking for is a function that will produce 'original_user' in the first case and 'my_test_user' in the second.

Solution

Update: you need the ORIGINAL_LOGIN fn here too

Original:

Afterwards, there is no matching system level user. So, it can't resolve the database level sid, so it simply returns the sid from sys.database_principals

CREATE USER my_test_user WITHOUT LOGIN;
SELECT USER_NAME(), USER, SUSER_SNAME(),SYSTEM_USER, suser_name();
EXECUTE AS USER = 'my_test_user' WITH NO REVERT;
SELECT USER_NAME(), USER, SUSER_SNAME(),SYSTEM_USER, suser_name(),
                        SUSER_SID();

SELECT * FROM sys.database_principals WHERE sid = SUSER_SID();

I don't know if this is by design, but it explains where the number comes from. The rest is as expected as explained below

Notes:

You'll get dbo for USER_NAME() because you are logged on with sysadmin rights. Everyone with "sysadmin" is dbo when using db level user functions.
After changing user context, db level user functions resolve to the database user context
For system level user functions, you'll get the login you used before