Why and how and where to use user without login
Imagine that you have somebody running an app, and this requires certain privileges. You do want to be able to log who is doing what and see who are logged in to your SQL Server. So each person need their own login (this can possible be one login created for a domain group).
You don't feel comfortable having these people connecting using SSMS and play around in the database. Since the app might need DELETE permissions on the Customers table (say), then the end-user now can do this from SSMS.
So you create a user without login and have the app use EXECUTE AS as this user. I.e., without EXECUTE AS, the end-user have no privileges at all in the database (i.e., the potential AD group you created a login for and its subsequent user in the database). But they do have privileges when they've done EXECUTE AS.
Is this read security? No, of course not. But perhaps better than either having no traceability or having the end-users being able to play around in the database &quot;directly&quot;.
What about application roles? Yes, that can be tighter since they are password protected.
Another usage for this is when you are testing things, using EXECUTE AS USER. You want to play around with something permissions related and it is easier to assign the permissions to a user without login and then do your tests then having to create a login and a user for whatever you want to test.