Fixing Orphaned Users
Its very common that few of your SQL Users become orphaned (Orphaned users are the users that are not able to connect to the database after you migrate your SQL Server Database from one SQL Server to another SQL Server). Today we will discuss how to fix orphaned users after we have migrated the sql database
The quickiest way to fix this is delete the user from the restored database and then recreate the user and corresponding permission to the database.
If the user owns a schema in the database, you won’t be able to delete the user. Then you can use the special stored procedure sp_change_users_login 'report'. This will give you a list of all the orphaned users in the database.
If you already have a login id and password for this sqlid, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'sqlid'If you want to create a new login id and password for this sqlid, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'sqlid', 'login', 'password'
This is the quickest way of fixing orphaned users.
