SQL Server Username 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 am considering using these accounts WITHOUT LOGIN
for auditing purposes. Basically, the "get database connection" code returns a connection that has already been made EXECUTE AS USER...WITH NO REVERT
.
The problem is that I cannot get consistent results from any of these username functions. Two lines of output:
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 ..
Functions USER
give correct result AFTER 'EXECUTE AS'
, but in advance they show dbo, not username Functions are SUSER
exactly the opposite - they are correct initially, but after impersonation they show some kind of identifier
The MSDN docs for SUSER_SNAME explicitly provide an example where this should work.
UPDATE: I'm looking for a function that will generate "original_user" in the first case and "my_test_user" in the second.
source to share
Update: you need the ORIGINAL_LOGIN fn here
Original:
Subsequently, there is no corresponding system-level user. So it cannot resolve the sid of the database, so it just 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 explains where this number comes from. The rest as expected is explained below
Notes:
-
You will get dbo for USER_NAME () because you are logged in as sysadmin. Each with "sysadmin" is a dbo when using db level UDFs.
-
After changing custom context db level custom functions resolve database user context
-
For system level custom functions, you will get the registration you used before
source to share
Non-logged users is a special case, used solely for providing brokerage services (binding remote services) or for code signing. They represent identity, not impersonation. Do not use no-logged users for EXECUTE AS. You will run into all types of edge cases, as they clearly do not have a user to login, and a mapping is expected almost everywhere.
source to share