Correct orphaned users in SQL Server

What is an 'orphaned' user?  A database user for which the corresponding SQL Server login is missing, or incorrectly defined, is called an 'orphaned user' -- and, this user cannot login to the SQL Server instance.  This can happen for a few different reasons, but mostly when the database is restored/attached from a different instance, but the corresponding login was never created.

Typically you'll find this after you've restored a database and a user calls to tell you they cannot login... but, you can also look for them with this statement after doing a restore:

-- find orphaned users

SELECT

       dp.type_desc,

       dp.SID,

       dp.name [user_name]

FROM

       sys.database_principals dp LEFT JOIN sys.server_principals sp 

      ON dp.SID = sp.SID 

WHERE

       sp.SID IS NULL 

    AND authentication_type_desc = 'INSTANCE';  

Next step is to resolve that orphaned user.  To do that you will run this CREATE statement in the master database with the SID returned from the above:

        -- resolve orphaned user

        CREATE LOGIN login_name

        WITH PASSWORD = 'strong_password', 

        SID = 'SIDfromAbove'; 

OR -- if the login DOES exist in the master database, then you will just use this statement to map the orphaned user back to that login in the database:

        ALTER USER [user_name] WITH LOGIN = [login_name];

I have used that last ALTER numerous times after restoring a database from one server to another, where the login already exists in the master, but the users cannot login to the restored database(s).  

Hope to have helped!