In this post I would like to focus on identifying orphan users and fix them in sql server. We have a need for this when we restore a database from a one instance to a different instance. In order to list the users along with their Security Identifiers aka SID in a database that is not associated with any login. We need to pass the parameter ‘Report’ to the SP_Change_Users_Login stored proc.
TO fix the orphan user issue we need to relink the server login account specified by <login_name> with the database user specified by <database_user> and this can be achieve with the following sql.
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>';
Once the above query is executed the user should be able to access the database.
Hope this helps.