One user for all database applications
The surrogate ID on sys.database_principals is an integer. This suggests a maximum user count of a billion-ish. The Maximum Capacity Specifications for SQL Server mentions no limit on logins or users that I can see. It does say there is a maximum of 32,767 connections at any one time. From any practical point-of-view there's nothing to stop you creating one user per application.

From a performance perspective, I've never seen an issue that resolves to too many user being defined. This is different too many being active. Each active connection consumes a small but finite resource. From what I remember this resource is not shared if different connections have the same user, so nothing to gain there.

(There is such a thing as connection pooling, however. To improve performance all connection requests must be constructed identically, however, including user names. This could work per application (with application-specific users) or per instance (with a shared user). To be effective developers must be meticulous with their connections.)

User credentials and permissions have to be stored somewhere. So, yes, having a ludicrous number of users will consume measurable resource. Since SQL Server is built to handle terabytes of data I doubt this will ever be a limiting factor. Considering how often authorisation is checked I'd imagine the storage and processing code paths have had a lot of optimization attention through the years. 

This is just a long way of saying many users equals more resource but you won't be able to measure the downside.

The overwhelming considerations for me are the benefits achieved in security and monitoring by having separate credentials.

A single user, used by all applications, must by definition have access to all the data. If there's a piece of data it doesn't need that data is not used by any application and shouldn't be in this database. That's a security risk. One breach by one bit of one application opens the whole data infrastructure to exploitation. In contrast having separate users which are granted only the SELECT or EXECUTE rights they explicitly require limits the damage any one application failure can expose.

Secondly - monitoring: something, somewhere will consume too much resource. Knowing it's running as user_generic is not helpful. Knowing it's running as user_nightly_product_load will tell you exactly which code to examine. The complement of this is Resource Governor which allows limiting a connection's access to CPU and memory, if that connection can be distinguished from others. The easiest way is to have separate user credentials.



Just for giggles I started a VM with SQL Server 2017 RTM, default installation. I set it to CREATE USER &lt;user_name&gt; WITHOUT LOGIN. The user names were created from a SEQUENCE.  It took a while, and the creation rate decreased over time, but it created over 270,000 users before I gave up. If there is a limit, it's greater than that.