How to impersonate a USER (not LOGIN) in Sql Server?

Question

I have a database which we want to create database USERS that are NOT mapped to server LOGINS. Then we want to have one server login, but it will EXECUTE AS USER = 'SomeUser' to run queries against that ONE db.

But when I try to run my sql block with the EXECUTE AS statement at the top, I am getting the error:

Cannot execute as the database principal because the principal "SomeUser" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I've granted the login IMPERSONATE on the user via:
GRANT IMPERSONATE ON USER::SomeUser TO [our-server-login]

Just for yucks, for testing (this is a test instance), I've even given the login IMPERSONATE ANY LOGIN permission, and that did not help.

The database actually already has SET TRUSTWORTHY ON, because it uses a number of .NET Assemblies, and that obviously is not making a difference.

Saw this question, which was fixed with TRUSTWORTHY but that is a different scenario (needing to impersonate logins across multiple dbs).

The db is running in 2019 compatibility mode on a Sql 2019 server.

Answer 1

Okay... so in digging in to put together code to repro this in isolation like Tibor asked in the comments, I wound up finding the problem.

This db was restored from a backup of a db from another server.

The user I was trying to impersonate was associated to a login on that other server. That login doesn't exist though on this server, but it still apparently had some sort of association to it.

So I went ahead and created a new login-less user, and tried that one, and the impersonation worked as expected, like a charm.

Then just to confirm, I deleted the user I was originally testing with, and recreated it fully as a login-less user, and now I can impersonate that one too.

So the issue was that even though the first user object did not have an associated login, it was still associated to a login, which did not exist. Hence the principal does not exist error.