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


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-SqlLogin 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-SqlOrphanUser 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

Repair-SqlOrphanUser -SqlServer sqlserver2014a -Databases db1 -Users OrphanUser

To find and repair user ‘OrphanUser’ on all databases

Remove-SqlOrphanUser -SqlServer sqlserver2014a -Users OrphanUser

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-SqlOrphanUser function

 Repair-SqlOrphanUser -SqlServer sqlserver2014a -RemoveNotExisting

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


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

Source Code

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

Related commands