How to tell if SQL Server user is contained?

Scenario and Initial Question

Imagine you sit down at the keyboard of an arbitrary SQL Server host (traditional SQL Server, on a machine; not Azure) that you've never used before. You have administrative credentials, and need to assess the security configuration of one of its databases.

• What query can you run to determine if a user in that database is a contained database user, or a traditional login-based user?

I'm specifically interested in Windows-authenticated users for my real-world project, but I'm generally interested in how this works. So, please elaborate on SQL- vs. Windows-authenticated, if it matters.
Tests

I've tried various methods of creating contained and login-based users (e.g. SQL, SSMS UI) and have explored various system views (e.g. sys.database_principals, sys.sysusers) and SSMS property pages, but no matter what I do, I can't figure out how to determine after-the-fact whether the user was created as contained or login-based.

One potential method would be to see if a login exists with the same SID as the user. If the user is functioning normally (e.g. able to connect) and there is no corresponding login, then the user must be contained.

But, is the converse true - if a login and a user with the same SID exist, does that imply that the user is based on that login? Let's look at some evidence.

Firstly, the doc states (Remarks section at bottom):

If there is a login in master database with the name name1 and you create a contained database user named name1, when a database name is provided in the connection string, the context of the database user will be picked over login context when connecting to the database. That is, contained database user will take precedence over logins with the same name.

The doc doesn't specify whether it applies to SQL-authentication, Windows-authentication, or both. If I'm reading the text correctly, though, we know that in at least some circumstances contained users and logins with the same name can coex

What actually makes a user contained or login-based?

A SQL user created in a contained database with the PASSWORD option is always contained. The authentication_type_desc column of sys.database_principals will show DATABASE and the contained database authenticates the user based on credentials stored in the database. A server principal (login) with the same name as the contained user is ignored.

The behavior of contained database Windows users isn't as straightforward. The CREATE USER syntax does not specify if the user is contained or not. The authentication_type_desc column of sys.database_principals will show WINDOWS regardless of whether or not the user is contained. The user is contained when no server principal exists with the same SID and the user is not a member of a Windows group with a login. Conversely, the user is not contained when a login with the same SID exists or the user is a member of a Windows group with a login.

do we have two separate things, a login and contained user, with the
same names and SIDs, or did we actually convert a contained user to a
login-based user by virtue of creating the login after the fact?

The implication with a Windows user in a contained database is that, unlike a contained SQL User, the user can be converted from/to a contained user by creating/dropping server principals.

I'm guessing that a "contained database user" isn't even really a
thing - meaning, there's no "IsContained" flag somewhere that tells us
whether a user is contained.

The authentication_type_desc DATABASE indicates a user is contained but one cannot determine if a user is contained when the value is WINDOWS.

StackExchange Database Administrators Q#328448, answer score: 4