The problem seems to be with the VALUES table value constructer

Replace your foj to derived table with this ...

full outer join

(SELECT 'dbo' name) b on a.name = b.name

.... and you get the expected output.

So what do I think is happening? I think it is somehow reconstructing that table on each fetch, throwing the query evaluation out of line with information it has cached. When you do a static cursor, it gets the output into a temp storage hence why that fixes it). Although the output is more like it did a cross join

I'd say that's a bug, but an easily avoidable one.

EDIT: To make things weirder, if you also add ('Guest') to the values collection, it works again. And by the estimated plan the original query (with the dynamic keyword) actually tries to do a keyset instead of dynamic. And when I add the second value it does snapshot instead of dynamic, hence why it works suddenly.