What am I missing?
The DOMAIN\user login should be able to run dbo.sp_HelloWorld with it's corresponding user in the MyDB context.
The EXECUTE AS USER command is the reason for the error being returned.
Execute as user documentation
Specifies the context to be impersonated is a user in the current
database. The scope of impersonation is restricted ...
This is standard SQL behaviour for pretty much all databases.
There are three ways how rows are returned:
without aggregate functions, the query returns one row per row;
with aggregate functions, but without grouping, the query returns exactly one row;
with aggregate functions and grouping, the query returns one row for each group (i.e., for each unique ...
In PostgreSQL, it is a pretty common set up to have "local" type set to "trust" or "peer", but "host*" types set to "md5" or the new "scram-sha-256" method. You seem to want the existence of a password to disable "trust", that would disable this setup as the password can't exist in one socket access method and not the other. I'd be pretty annoyed to have ...
I'd strongly consider using PostgreSQL, as it supports Row Security (as well as Column Security), and is free and well made
You should also consider using real database users, but you don't have to. For example, you could set the current user into a temp table upon sign-in, and reference that username in your RLS policy