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?
- 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.
- 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:
- 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.
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