Orphan Users

Overview

A database user is orphaned when no login on the instance carries its SID. The user is still in the database, still in its roles, still owning whatever it owned, and nobody can connect as it.

This normally arrives all at once. A database is restored onto a server that does not have the same logins, and every user in it is stranded together. So the question the report has to answer is not “which users are broken” – it is which single fix clears the most.

The Orphan Users report: the reconciliation map over the findings grid
Five accounts, twelve stranded users, four databases. The long blue row at the top is one account that one statement puts back everywhere.

The three answers

Every orphan lands in exactly one of three buckets, and the bucket is what the colour means.

Verdict What it means What it takes
Remap to the matching login A login of that name is already on this instance. Only the SID disagrees. ALTER USER [x] WITH LOGIN = [x]
Login must be created Nothing on the instance carries that name, and the user holds something worth keeping. CREATE LOGIN, keeping the original SID
Dormant, safe to drop No roles beyond public, owns nothing, has no permission granted to it. DROP USER [x]

Two more things are drawn on top of the verdict rather than instead of it, because they are facts about a user rather than a bucket it sits in:

  • A red ring means the orphan holds db_owner, db_securityadmin, db_accessadmin, db_ddladmin, or a granted CONTROL. That is the one on this page worth a decision rather than a fix, because a dormant privileged user is inherited whole by whoever creates a login with that name next.
  • A notch cut out of the corner means the user owns schemas or objects, so DROP USER cannot succeed until the ownership moves.

Where to find it

An instance level report. Right-click the server → Instance Level Reports → Orphan Users.

It is an instance level report even though what it finds lives in databases, because an orphan is a mismatch between a database user and the server’s logins, and only the server can see both sides of that.

The three views

The toolbar switches between three readings of the same scan. None of them goes back to the server.

Map

The reconciliation map
The reconciliation map

One cell per orphaned user, at the intersection of the account and the database it is stranded in.

Rows are ordered by how many databases the account is broken in, columns by how many orphans each database holds, so the two shapes that decide the fix arrive at the top left without being looked for:

  • A long row is one account broken across many databases. Putting one login back clears every one of them.
  • A deep column is a database that came from another server and brought its users without their logins. Script the logins across rather than fixing users one at a time. An amber rule over a column means msdb has a restore recorded for that database, which is usually the whole explanation.

The bars on the right are the blast radius per account. The bars underneath are orphans per database. Everything on the chart is a filter: click a cell, a row label, a column label or a legend swatch and the grid holds only those rows. Clicking the same thing again clears it.

Identities

The same users folded to one row per account, ranked by what fixing it clears, with the statement it resolves to, the databases it is broken in, and what it still holds. This is the view that answers “what do I actually type”.

Triage

The whole instance as one bar split three ways, with the statement under each segment. This is the view that fits in a screenshot on a change ticket. When anything privileged was found, the strip underneath names it.

The grid

The findings grid
One row per orphaned user. The Action column is coloured the same three ways as the chart, a role in red is a privileged one, and Owns is what has to move before a drop can succeed.
Column What it holds
Database The database the user is stranded in
User The database principal, as sys.database_principals has it
Type SQL user, Windows user, or Windows group
Action One of the three verdicts, coloured
Login match same name, different SID when there is something to remap onto, no login of this name when there is not, and disabled when the login is there but switched off
Roles Database roles the user holds, in red when one of them is privileged
Owns Schemas and objects, which is what decides whether a drop can proceed
Grants Permissions granted directly to the user, not counting the automatic CONNECT
Created When the user was created in that database

Fixing everything at once

The usual cause of this page having anything on it is one restore, so the usual fix is one action. Attempt to fix all orphan users is on the toolbar as Fix all, on the right-click menu whether or not anything is selected, and on a right-click anywhere on the chart.

It works on the rows the page is currently showing, which is what makes the chart a way of scoping it: click one database on the map and Fix all means that database. The item counts what it can do before you press it, and greys itself out when the answer is none.

It does It does not
Remaps every user that has a login of its name already on the instance Create a SQL login. The password is not recoverable from anywhere on this instance, and inventing one would hand out an account nobody asked for
Creates every missing Windows login, once per name however many databases the account is stranded in Drop the dormant users. Dropping a principal is the one thing on this page that cannot be undone from here, so it stays a per row decision

Both exclusions are named in the dialog afterwards, with a count, so a page that still has rows on it after Fix all says why. Script the fix for all orphan users writes the whole plan out instead, including the CREATE LOGIN statements with their original SIDs and a placeholder for each password.

The right-click menu

Every action is offered only on rows it applies to, and acts on everything selected, not just the row under the cursor.

Action Offered when
Attempt to fix all orphan users Always, selection or no selection. See above.
Script the fix for all orphan users to the clipboard Always.
Attempt to map orphan database users to matching SQL login A login of that name is on the instance. Runs ALTER USER ... WITH LOGIN per selected row.
Create the missing Windows login The orphan is a Windows user or group. A Windows login needs no secret, so this is complete. Only ever run once per name however many rows are selected.
Attempt to replace object ownership with [dbo] The user owns at least one schema. Moves them all to dbo.
Attempt to remove orphan database user if it is not mapped to any objects Rows are selected. Users that still own something are left alone and the dialog names what is in the way, instead of raising error 15138.
Script the fix for selection to the clipboard Rows are selected.
Copy this finding, Copy every finding shown Always.
Copy Chart to Clipboard Always.

A right-click on a cell in the map offers the same actions scoped to that one user, plus Attempt to fix account in all N databases for the whole row.

A SQL login cannot be recreated by this report. The password is not recoverable from anywhere on the instance, so CREATE LOGIN for a SQL user is written to the clipboard with a placeholder rather than executed. Keeping the original SID in that statement matters: a login recreated with its original SID re-links the user in every database at once, with no ALTER USER needed afterwards.

What this replaces

The old page was a four column list – a row counter, database, username, and a column that printed the literal text -- None Found -- on most rows. There was no chart, no colour, no severity and no count, and one account broken in nine databases arrived as nine near identical rows with nothing to say they were one account.

The scan underneath it had five problems, and they are why this is a rewrite rather than a restyle:

  • It used sp_MSforeachdb, which is undocumented, is known to skip databases, and fails outright on any database the connection cannot USE.
  • It built its work tables as global temp tables, so two people running the report against one instance collided with each other.
  • It asked sp_change_users_login, deprecated since SQL Server 2005, which reports SQL authenticated users only. A Windows user or an AD group whose account was deleted is orphaned in exactly the same way and never once appeared on this page. The new scan joins sys.database_principals to sys.server_principals on SID, so it finds them.
  • Read only, offline and restoring databases were dropped from the scan silently. The footer now says how many were checked and names what was skipped.
  • All three fix actions built their T-SQL with a text substitution on the words DatabaseName and UserName, so a name carrying a space or a bracket produced a batch that failed or did something else. Every identifier now goes through QUOTENAME.

What is deliberately not reported

Not an orphan Why
Contained database users A user created WITH PASSWORD or FROM WINDOWS inside a partially contained database is supposed to have no login.
Users created WITHOUT LOGIN Deliberately login-less, usually for EXECUTE AS.
Built in principals MS_DataCollectorInternalUser and its kind are made by SQL Server for itself.
dbo, guest, sys, INFORMATION_SCHEMA Fixed principals in every database.

All four are excluded on authentication_type, which arrived in SQL Server 2012. On SQL Server 2008 and 2008 R2 there is no such column, so a user created WITHOUT LOGIN there is reported.

Messages you may see

Message What it means
No orphaned users on this instance Every database user on every database checked has a login behind it. The good outcome, and the common one.
This connection cannot see the logins on this instance Without VIEW ANY DEFINITION or a server role that includes it, sys.server_principals comes back empty and every user would be reported as an orphan. The report refuses to draw rather than cry wolf.
N skipped: X is not online The database is offline, restoring, or in recovery. Nothing can be read from it.
N skipped: X is read only An orphan in a read only database is still an orphan, but nothing can be fixed until the database is writable.
N skipped: X has no access for this login HAS_DBACCESS said no.
Report Why
Security Posture The instance wide security score, of which orphaned users are one CIS item
Logins for Instance The other side of the join: what logins exist, and what they can reach
QuickScan Report The wider health check, which flags orphans and links here
Last DBCC CheckDB Known Good The other page that a restore from another server makes interesting

Frequently asked questions

Why does one account show as needing a login when it is dormant in most databases? Because the account as a whole still holds something somewhere. The action on the Identities view is the most actionable verdict any of its cells carries, not the commonest one – an account holding db_owner in one database and nothing in three others still needs its login back, and a row headed DROP USER would be saying the opposite. The Map shows the per database verdict.

Is remapping reversible? ALTER USER ... WITH LOGIN changes which login the user maps to and touches nothing else. No permission, no role membership and no ownership changes.

Why is the Grants column zero on rows that clearly have permissions? It counts permissions granted directly to that user, and it deliberately excludes the CONNECT that CREATE USER grants to everything it makes. Permissions held through a role are in the Roles column.

The scan is slow. It walks every online database and reads the role memberships, the owned schemas and the granted permissions in each one. On an instance with a few hundred databases that is minutes. The timeout is set to five minutes for that reason.

Can I fix everything at once? Yes. Fix all on the toolbar, or Attempt to fix all orphan users on the right-click menu. It does the remappings and the missing Windows logins and tells you what it left. Script every fix writes the same plan out instead, which is worth preferring on anything you have not looked at yet.