Orphaned users are database users that exists in database but not linked with login, this happens due to action like restoring a database from one server to another, during failover database to DR or deleting login from instance.
Database User : A user account that has access to a database.
SQL Login : n account that provides authentication to the SQL instances and grant access to databases users.
What happens if Logins in not present ?
User wont be able to access database creating Orphaned users
Now the big question how to Fix Orphaned Users
- Identify Orphaned Users
EXEC sp_change_users_login 'Report';- Fix Orphaned Users by Mapping
Exec sp_change_users_login 'Auto_Fix','Orphaned_user','login','password'- Manual Mapping to a Specific Login
Exec sp_change_users_login 'Update_One', 'Orphaned_user', 'login'Orphaned users are common issues while doing DR exercise, migrations or database restores, so re-mapping them is a standard part of database maintenance.