Every wonder who all your orphaned SQL Server users are on your server? Just run the script below and copy the results of the query into a new query window then execute them. Make sure you keep the spacing and the quotes as they are so that the Dynamic SQL generated is free of Syntax errors.
USE master
SELECT 'USE '+ name+ ' EXEC sp_change_users_login ''Report'' '
FROM sys.databases
WHERE name not in ('master','msdb','model','distribution','tempdb')
ORDER BY name
But wait a minute, I only know which orphaned users are on my server. I want to actually fix them! Ok, ok relax, I have the solution for that just keep reading.
Please know what you’re doing before you start running the code below; this is also known as a disclaimer that relieves me of responsibility 
/*Run this first*/
CREATE DATABASE OrphanedUsers
/*Run this to create a table to store the orphaned users*/
USE OrphanedUsers
CREATE TABLE tblOrphanUsers
(username varchar(100),
UserSid varchar(max),
dbname varchar(200) default db_name()
)
/*Create Dynamic SQL to insert all the orphaned users from all the databases into the new table that was created*/
USE master
SELECT 'USE '+ name+ ' INSERT INTO OrphanedUsers.dbo.tblOrphanUsers (UserName,UserSID) EXEC sp_change_users_login ''Report'' '
FROM sys.databases
WHERE name not in ('master','msdb','model','distribution','tempdb')
ORDER BY name
/*Now copy the query results into a new query and run*/
/*After you run those queries in a new window, your table will be populated with all the orphaned users in every database on your server*/
/*Now that you have all the orphaned users in a table, you can use some more Dynamic SQL to generate code that will fix them*/
SELECT 'USE '+DBname+ ' EXEC sp_change_users_login ''Auto_Fix'','''+username+''' '
FROM OrphanedUsers.dbo.tblOrphanUsers
/*Now copy the results into a new query and run*/
/*Cleanup after yourself please!*/
DROP DATABASE OrphanedUsers
Now if you get an error like this:
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 214
An invalid parameter or option was specified for procedure ‘sys.sp_change_users_login’.
Then you will have to manually fix that login. Generally that error is due to the fact that a SQL Login does not exist for that database user and therefore it must be created with a password. So if you don’t know the password you are potentially out of luck. Anyway, here is a code snippet to demonstrate how to make that manual fix.
USE [DatabaseName] EXEC sp_change_users_login 'Auto_Fix','DBUserName',NULL ,'password$fHO'
Running this script will also create a SQL Login at the server level with the new password. The first parameter indicates it is an Auto Fix operation, the second parameter is the database user name that is problematic. The third parameter should be set to NULL and the fourth is the password that will be assigned to the new Login created. The SQL login will take the same name as the DB user.
