dbatools is a free PowerShell module with over 200 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


Repair-DbaOrphanUser

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.

Screenshots

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

Examples

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.

Get-Help

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