Open source · dbatools

Find and fix orphaned users in SQL Server with dbatools

After a restore on another server, an application can no longer log in, although the login and the database user have the same name. Usually the user is orphaned.

Tested with dbatools 2.9.1 on Nov 18, 2026 · approx. 7 min read

Part 6 · OrphansSQL query 1Lab Windows · Linux
Repair-DbaDbOrphanUser -RemoveNotExistingcalled directlyaggkassiererkassierereesdbt_winwaisedbt_winwaisea mapped, s stays (schema with a table)dropped without asking: g, kassierer, e, dbt_winwaisecalled from a functionagkassiereresdbt_winwaiseNo orphan users found on database [orph3].nothing droppedno warning, no errorlab · PowerShell 5.1 and 7.6

A login lives on the instance, a user in the database. The two are linked by the SID, not by the name. If a user's SID matches no login on the instance, the user is orphaned: it still has its permissions in the database, but nobody can log in as that user. This happens mainly in three cases:

  • A database is restored on an instance where its logins are missing.
  • Logins are created by hand or with Copy-DbaLogin -NewSid and get a new SID in the process.
  • A login is dropped, and its user stays in the database.

How to transfer logins with the same SID and so avoid most orphans is shown in part 5.

dbatools has three commands for this: Get-DbaDbOrphanUser, Repair-DbaDbOrphanUser and Remove-DbaDbOrphanUser. In our lab, the first one found every orphaned user. Mapping, however, worked by name only, and one cleanup option deleted more than its name suggests, or nothing at all from scripts. This page shows what worked in the lab and where you should check. Tested with dbatools 2.9.1 on Nov 18, 2026 on Windows with two instances on one server and against SQL Server 2022 in a Linux container (method). How to install dbatools and connect securely is covered in part 1.

In 60 seconds

  • Finding is reliable. Get-DbaDbOrphanUser reports orphaned SQL users as well as Windows users and groups. Users that were deliberately created without a login are not reported.
  • Mapping works by name only. Repair-DbaDbOrphanUser looks for an active login with the same name. Users whose name differs from their login, or whose login is disabled, stay orphaned.
  • -RemoveNotExisting deletes everything left over, including those two cases, without -Force. Deleted users do not appear in the output.
  • Called from a function, the same option deletes nothing, without a warning and without an error.
  • Look first, check afterwards: Remove-DbaDbOrphanUser -WhatIf names the candidates; Get-DbaDbOrphanUser shows what is still there after every automated run.

1 · Finding orphaned users

The commands on this page (2.9.1 help and source code):

CommandEffect
Get-DbaDbOrphanUserlists orphaned users
Repair-DbaDbOrphanUsermaps orphaned users to logins with the same name; -RemoveNotExisting deletes the rest
Remove-DbaDbOrphanUserdeletes orphaned users; with -Force also those with a login of the same name or with schemas that contain objects

The database kasse comes from the lab in part 5: a Windows server with the instances SQLSRV-01 and SQLSRV-01\SQL2. kasse contains the users app_leser, gesperrt (login disabled), kassierer (login called alias_login), ehemalig, a local Windows user and a local Windows group. We restored kasse on SQLSRV-01\SQL2 as kasse_vorher before the logins were there:

Get-DbaDbOrphanUser -SqlInstance SQLSRV-01\SQL2 -Database kasse_vorher | Format-Table DatabaseName, User -AutoSize
Lab output
DatabaseName User
------------ ----
kasse_vorher app_leser
kasse_vorher ehemalig
kasse_vorher gesperrt
kasse_vorher kassierer
kasse_vorher SQLSRV-01\dbt_wingruppe
kasse_vorher SQLSRV-01\dbt_winuser

The command finds orphaned SQL users as well as Windows users and groups whose login is missing. Users that were deliberately created without a login (CREATE USER … WITHOUT LOGIN) are not reported, and rightly so.

Which user belongs to which login is shown by a query on the SID. After copying the logins with Copy-DbaLogin and restoring kasse, it looked like this:

-- Users of a database and their login by SID (read-only)-- MAXDOP 1: runs serially and does not occupy further cores of the applicationSELECT dp.name AS benutzer, dp.type_desc, DATALENGTH(dp.sid) AS sid_bytes, sp.name AS login_per_sidFROM sys.database_principals AS dpLEFT JOIN sys.server_principals AS sp ON sp.sid = dp.sidWHERE dp.type IN ('S', 'U', 'G') AND dp.principal_id > 4ORDER BY dp.nameOPTION (MAXDOP 1);
Lab output
benutzer                type_desc     sid_bytes login_per_sid
--------                ---------     --------- -------------
app_leser               SQL_USER             16 app_leser
ehemalig                SQL_USER             16
gesperrt                SQL_USER             16 gesperrt
kassierer               SQL_USER             16 alias_login
SQLSRV-01\dbt_wingruppe WINDOWS_GROUP        28 SQLSRV-01\dbt_wingruppe
SQLSRV-01\dbt_winuser   WINDOWS_USER         28 SQLSRV-01\dbt_winuser

Through its SID, kassierer belongs to alias_login. ehemalig was already orphaned on the source; its login had been dropped. Because Copy-DbaLogin carries the SIDs over, there was nothing else to repair here. (The column name benutzer is German for user; we kept it as in the lab.)

2 · Mapping: Repair-DbaDbOrphanUser works by name only

If the login was not copied but created by hand or with -NewSid, the SIDs do not match. That is where Repair-DbaDbOrphanUser comes in. For every orphaned user, it looks for an active login with the same name and sets the mapping with ALTER USER … WITH LOGIN (source code, lines 160–224). On SQL Server 2022, with logins of the same name but a different SID:

Repair-DbaDbOrphanUser -SqlInstance sql2022 -SqlCredential $cred -Database kasse_b
UserLogin on the destinationStatus
app_leser, app_schreibersame name, activeSuccess
gesperrtsame name, disabledNo matching login
kassierercalled alias_loginNo matching login
ehemalignoneNo matching login

So a disabled login does not count as a match, and a user with its own name does not find its login. Both stay orphaned. That is cautious and fine in itself. It only becomes dangerous with -RemoveNotExisting.

3 · -RemoveNotExisting: look first, check afterwards

With -RemoveNotExisting, Repair-DbaDbOrphanUser deletes all users it could not map, together with their permissions in the database. In kasse_b from section 2, that includes gesperrt, whose login is merely disabled, and kassierer, whose login merely has a different name.

Look first. -WhatIf on Repair does not say whom it would hit. Remove-DbaDbOrphanUser -WhatIf names the users:

Repair-DbaDbOrphanUser -SqlInstance sql2022 -SqlCredential $cred -Database kasse_b -RemoveNotExisting -WhatIf
Remove-DbaDbOrphanUser -SqlInstance sql2022 -SqlCredential $cred -Database kasse_b -WhatIf
Lab output
What if: Performing the operation "Remove-DbaDbOrphanUser" on target "kasse_b".
What if: Performing the operation "Dropping schema 'ehemalig'." on target "kasse_b".
What if: Performing the operation "Dropping user [ehemalig]" on target "kasse_b".
What if: Performing the operation "Dropping user [gesperrt]" on target "kasse_b".
What if: Performing the operation "Dropping user [kassierer]" on target "kasse_b".

The first line comes from Repair, the others from Remove-DbaDbOrphanUser.

What actually happens. For the cross-check, a test database on SQLSRV-01\SQL2 had six orphaned users: a (login of the same name, active), g (login disabled), kassierer (login called alias), e (no login, owns an empty schema e), s (no login, owns the schema lager with one table) and the Windows user SQLSRV-01\dbt_winwaise (Windows account present, login dropped).

Repair-DbaDbOrphanUser -SqlInstance SQLSRV-01\SQL2 -Database orph1 -RemoveNotExisting -Confirm:$false
Lab output
ComputerName : SQLSRV-01
InstanceName : SQL2
SqlInstance  : SQLSRV-01\SQL2
DatabaseName : orph1
User         : a
Status       : Success

ComputerName      : SQLSRV-01
InstanceName      : SQL2
SqlInstance       : SQLSRV-01\SQL2
DatabaseName      : orph1
SchemaName        : e
Action            : DROP
SchemaOwnerBefore : e
SchemaOwnerAfter  : N/A

[09:21:17][Remove-DbaDbOrphanUser] Schema 'lager' owned by user s have 1 underlying objects. If you want to change the schemas' owner to 'dbo' and drop the user anyway, use -Force parameter. Skipping user '[s]'.

Afterward, only a and s were left. a was mapped to its login. Deleted were e with its empty schema, g, kassierer and the Windows user, without -Force. The only thing that stopped the command was the schema with content. With -Force, dbo took over the schema lager, and s was deleted as well. The command returns no result object of its own for deleted users; only with -Verbose do they appear as "User [g] was dropped …". If you only read the output, you do not see the deletions.

Our recommendation: run Repair-DbaDbOrphanUser without -RemoveNotExisting, look at the leftovers with Remove-DbaDbOrphanUser -WhatIf and decide one by one. Users of disabled logins are often intentional, for example for locked accounts that will be released later.

This is what the call inside a function looked like, with -Verbose:

function Bereinige {
    Repair-DbaDbOrphanUser -SqlInstance SQLSRV-01\SQL2 -Database orph3 -RemoveNotExisting -Confirm:$false -Verbose
}
Bereinige
Lab output
[09:22:22][Repair-DbaDbOrphanUser] Validating users on database '[orph3]'.
…
[09:22:23][Repair-DbaDbOrphanUser] User 'a' mapped with their login.
Performing the operation "Remove-DbaDbOrphanUser" on target "orph3".
[09:22:23][Repair-DbaDbOrphanUser] Calling 'Remove-DbaDbOrphanUser'.
[09:22:24][Remove-DbaDbOrphanUser] Validating users on database [orph3].
[09:22:24][Get-DbaDbOrphanUser] Validating users on database '[orph3]'.
[09:22:24][Get-DbaDbOrphanUser] Orphan users found
[09:22:24][Remove-DbaDbOrphanUser] No orphan users found on database [orph3].

Get-DbaDbOrphanUser finds the users; one line later, Remove-DbaDbOrphanUser no longer does. In a direct call, this is where Call origin: Repair-DbaDbOrphanUser. appears, followed by the deletions.

What we measured and what we did not

  • Measured on Windows (Nov 18, 2026): dbatools 2.9.1 under Windows PowerShell 5.1 on Windows Server 2022 with two instances of SQL Server 2022 RTM on the same server, Windows authentication. Orphaned SQL users, a local Windows user and a local Windows group after a restore; -RemoveNotExisting in six ways of calling it. The VM had one vCPU; we therefore do not quote run times.
  • Also measured (Nov 18, 2026): dbatools 2.9.1 under PowerShell 7.6.8 in a Linux container against SQL Server 2022 CU27 in a Linux container: mapping with Repair-DbaDbOrphanUser, -WhatIf and -RemoveNotExisting per call level. One run per variant, small test databases, throwaway lab logins only.
  • Source code and issues: We read the cited places in the 2.9.1 source, not the whole module, and searched the issues and pull requests on GitHub for reports on Nov 18, 2026.
  • Not measured: versions other than SQL Server 2022, domain accounts and groups, two separate Windows servers, contained databases, case-sensitive collations and Azure SQL Managed Instance.

Further reading

More parts of the series