SQL Server user name functions -- CONFUSED

Question

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.

Any ideas? Thanks for any help you can provide.

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.

Answer 1

Just a guess here, but it looks like to me that the first select is being run as the user you're currently logged into as with the connection, and in the second select you are then telling sql server to execute as the newly created user.

Answer 2

Users without login are a special case used exclusively for service broker security (remote service bindings) or for code signing. They represent identity, not impersonation. Do not use users without login for EXECUTE AS. You'll run into all sort of edge cases because they explicitly don't have a user to login mapping and almost everywhere a mapping is expected.

Answer 3

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