CREATE USER WITHOUT LOGIN vs Contained Database Users

---

Two Very Different Accounts That Share One Keyword

CREATE USER builds three quite different things depending on the clause you finish it with, and
two of them are easy to confuse because both produce a database user with no server login behind it:

CREATE USER [AppUser]  FOR LOGIN [AppUser];              -- mapped to a server login
CREATE USER [AuditRole] WITHOUT LOGIN;                   -- cannot authenticate, by design
CREATE USER [AppUser]  WITH PASSWORD = 'Str0ng!Pass';    -- contained: authenticates INSIDE the database

The middle one and the last one both look like “a user without a login” if you only read the row in
sys.database_principals. They are opposites. One is an identity you deliberately made
unauthenticatable so it can only ever be borrowed by code. The other is a fully independent account
that carries its own password and logs in on its own.

Confusing them is not academic. It is a specific, silent way to break a migration, and it is the
kind of break where every statement succeeds.

---

How SQL Server Actually Tells Them Apart

The column that matters is authentication_type_desc in sys.database_principals. type_desc will
not help you: all three read SQL_USER.

Here is the real output from an instance with one of each created in a CONTAINMENT = PARTIAL
database:

name                type_desc   authentication_type_desc
zzContainedPwUser   SQL_USER    DATABASE
zzMappedUser        SQL_USER    INSTANCE
zzNoLoginUser       SQL_USER    NONE

authentication_type	What it is
INSTANCE	An ordinary database user mapped to a server login. Authentication happens at the server, and the user is bound to the login by SID, not by name.
DATABASE	A contained user. It holds its own password inside the database and authenticates directly against it, with no server login involved at all. Requires CONTAINMENT = PARTIAL and the instance-level contained database authentication option.
NONE	A WITHOUT LOGIN user. It has no authentication path whatsoever and no password. Nobody can ever connect as it. That is the entire point of it.
WINDOWS	A Windows principal mapped directly inside a contained database, rather than through a server login.

Query it yourself:

SELECT name,
       type_desc,
       authentication_type_desc,
       create_date
FROM   sys.database_principals
WHERE  type IN ('S', 'U', 'G')
  AND  name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')
ORDER BY authentication_type_desc, name;

---

What WITHOUT LOGIN Is Actually For

A WITHOUT LOGIN user is not a broken account or a leftover. It is a deliberate construct for
impersonation: a named bundle of permissions that code can borrow with EXECUTE AS, without
anyone ever being able to log in as it.

CREATE USER [ReportRunner] WITHOUT LOGIN;
GRANT SELECT ON SCHEMA::Reporting TO [ReportRunner];

-- A procedure can now run with exactly those rights and no more
CREATE PROCEDURE Reporting.usp_RunMonthly
WITH EXECUTE AS 'ReportRunner'
AS
    SELECT * FROM Reporting.MonthlySummary;

Verified behaviour: EXECUTE AS USER = 'ReportRunner' switches the security context successfully,
USER_NAME() returns ReportRunner, and ORIGINAL_LOGIN() still returns the real login underneath,
so the audit trail survives the impersonation. REVERT switches back.

The security value is that there is no password to leak, no account to be brute forced, and no way
to connect as it directly. The permissions only exist while borrowed. It is the right answer when
you want a procedure to do something the caller cannot do on their own.

---

What a Contained User Is For

A contained user goes the other way: it makes the database self-sufficient. Authentication moves
inside, so the database can be detached and attached, or failed over to a completely different
instance, and its users still work with no login to create anywhere.

That is why they show up in Availability Group setups and in anything that moves between
environments regularly. It is also why they are invisible to every login-migration script in
existence: those scripts read sys.server_principals, and a contained user is not there.

-- Requires: sp_configure 'contained database authentication', 1
-- and the database created or altered to CONTAINMENT = PARTIAL
CREATE USER [AppUser] WITH PASSWORD = 'Str0ng!Passw0rd';

---

The Silent Migration Break

Here is where the confusion becomes a real defect, and it is one I found in my own generator script,
not in theory.

If a tool sees a database user whose SID does not match any server login, the tempting conclusion is
“this user has no login, so script it as WITHOUT LOGIN“. That produces a statement which is valid,
runs cleanly, reports success, and creates the user on the target with every role membership intact.

It also converts an account that authenticated with its own password into one that can never
authenticate again.

Nothing fails. There is no error, no warning, and the user is present in sys.database_principals
afterwards, so a post-migration check that counts users passes. The application finds out.

Running the exact branch logic against a real contained database showed both halves of the mistake:

name                auth_type   what the naive generator emitted
zzContainedPwUser   DATABASE    CREATE USER [zzContainedPwUser] WITHOUT LOGIN      <- wrong
zzMappedUser        INSTANCE    CREATE USER [zzMappedUser] FOR LOGIN [DemoAppUser] <- correct
zzNoLoginUser       NONE        -- SKIP (no matching server login)                 <- also wrong

Read those two wrong rows together, because the symmetry is the lesson. The branch fired
WITHOUT LOGIN for the user it should have refused to script, and refused the user that genuinely
was WITHOUT LOGIN. The one case where the statement is correct is the one case it never emitted.

The fix is not a smarter guess. A contained user’s password cannot be read from the source at any
permission level, so there is no faithful CREATE USER to generate, and the honest output is a
comment naming what a human has to do:

-- MANUAL: [AppUser] is a contained user (authentication_type = DATABASE).
-- Its password cannot be read from the source, so it is not scripted here.
-- Recreate it on the target with the password from your credential store:
--   CREATE USER [AppUser] WITH PASSWORD = N'ENTER_PASSWORD_HERE';
-- Target database must have CONTAINMENT = PARTIAL.

A statement that runs and quietly changes how an account authenticates is worse than no statement
at all, because nothing fails and nobody looks again.

---

Finding Them Before They Bite

Run this on every database before a migration, an AG build, or an audit:

SELECT DB_NAME()                AS database_name,
       name                     AS user_name,
       authentication_type_desc,
       CASE authentication_type_desc
            WHEN 'DATABASE' THEN 'Contained user, has its OWN password. No login script will carry it.'
            WHEN 'NONE'     THEN 'WITHOUT LOGIN, impersonation only. Script it as WITHOUT LOGIN.'
            WHEN 'INSTANCE' THEN 'Mapped to a server login. Check the SID matches after migrating.'
            ELSE 'Windows principal mapped inside the database.'
       END AS what_to_do
FROM   sys.database_principals
WHERE  type IN ('S', 'U', 'G')
  AND  name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')
  AND  authentication_type_desc IN ('DATABASE', 'NONE')
ORDER BY authentication_type_desc, name;

Anything returning DATABASE needs its password from wherever your organisation keeps secrets, and
it needs to go in the runbook as a manual step. Anything returning NONE is safe to script exactly
as it is.

---

Frequently Asked Questions

---

Related Reading

• SQL Server Login Migration: What Gets Silently Left Behind, the wider list of what a login transfer drops
• DBA Scripts: Get Login Migration Parity, comparing two servers attribute by attribute
• Generate User Mapping Script, the generator this defect was found in
• Fix Orphaned Users
• Get Permissions and Role Membership
• SQL Server Orphaned Users (Error 15023)

---

Summary

Three statements, one keyword, three different kinds of account. FOR LOGIN maps to the server and
is bound by SID. WITH PASSWORD makes a contained user that authenticates inside the database and
which no login-migration script will ever see. WITHOUT LOGIN makes an identity that deliberately
cannot authenticate at all, for code to borrow with EXECUTE AS.

Read authentication_type_desc, not type_desc, and treat DATABASE as a manual step in every
migration runbook. The password is not readable, so any tool that appears to script one for you has
quietly turned it into something else.