dbatools is a free PowerShell module with over 300 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


What is an orphaned user?

An orphaned user is a user on a database that has no corresponding SQL Server login.

Where did the orphaned user come from?

  1. Sometimes we need to refresh a environment with a copy from another environment, or we found a specific behavior that can only be reproduced on that database and we are asked for a backup to give to the DEV team so they can analyse.
    If you refresh/restore the database on a different instance the users will not have their matching login (even if exists the same name) because the login SID (Security Identifier) will not be the same.
  2. Another scenario is when the login was deleted but the user that uses that login was not removed. Even if you create a new login with the same name, the SID will be different (assuming we do not force it). Note: You can create a login with the same SID if you want. Our function Copy-DbaLogin do that, create the login on the destination server with the same SID existing on the source server.

How does this function work?

To map our orphan user to an login it must be:

  • Enabled
  • Not a system object
  • Not locked
  • Have the same name that user

NOTE: You can also drop users that does not have their matching login by specifying the parameter -RemoveNotExisting
This will be made by calling Remove-DbaOrphanUser function.


Repairing all users within database ‘db1’
Repairing all users within database ‘db2’. Some users does not have their matching login.
Repairing all users within database ‘db1’. Forcing remove of orphan users that do not have matching login


To find and repair user ‘OrphanUser’ on ‘db1’ database


To find and repair user ‘OrphanUser’ on all databases


To find all orphan users of all databases present on server ‘sqlserver2014a’, and also remove all users that does not have their matching login by calling Remove-DbaOrphanUser function


This command was created by Cláudio Silva. You can find Cláudio on Twitter and LinkedIn.


From PowerShell, execute Get-Help Repair-DbaOrphanUser -Detailed for more information on this function.

Source Code

Want to see the source code? View Repair-DbaOrphanUser.ps1 on GitHub

Related commands