The SQL you have will work, however, I doubt it's what you're after. I've added a couple of extra aliases and a comment to explain this:

The fact that they are using it suggest that the view (I assume it's a view with a suffix of _vw), performs poorly with the new estimator. I think the...

It's the users you need to change, not the logins. A user is a database object, while a login in Server based.

You could use some dynamic SQL to...

I can't see any sample data in your SQL, only DDL definitions. I'll need sample data and expected results too please (note that many users, including myself, are unhappy to...

Really need to have sample data and expected result now. And, if you have 2 status it can be, have you considered using IN in the CASE expression?

YOu really need to get away from using a subquery for EVERY expression (I.e. a WHERE, a JOIN, etc it's awful practice and makes your SQL very difficult to read....

Have you tried anything so far? If not, consider the simple + operator and FOR XML PATH. Have a go, and if you get stuck please do reply with your...

It's worth noting that if you've upgraded your instance to 2017 (from 2016 or prior), Microsoft also recommend you regenerate your Service Master Key. To quote the documentation: