Loginless users in SQL Server 2005+

MSDN

When you create a user without a login, there is no path that allows a login to be mapped to that particular user in the database.  The only way to "utilize" this particular user in the database is through impersonation.  Again this is BY DESIGN.  Loginless users were added in SQL Server 2005 as a replacement to application roles.  The main reasons behind this were that you did not need to embed a password into an application and you also did not suddenly have multiple people coming into your SQL Server all with the same "login".  By utilizing loginless users, the user's of an application login to the SQL Server using THEIR login credentials (NOT a generic login), so that you can audit activity to a given login.  Once they change context to the application database, you can issue an EXECUTE AS command to change their user context thereby giving them the authority they need within the application.

Testing execute as ->

create user deep without login;

USE DEEPTEST;

GO

CREATE PROCEDURE dbo.usp_Demo

WITH EXECUTE AS 'deep'

AS

SELECT user_name();

GO

USE DEEPTEST;

exec usp_Demo ->  deep
SELECT user_name(); -> dbo