When you migrate a database to a new server, you may end up with a set of orphaned users. These users are part of the specific database but not part of the Db server.
To fix this, do the following:
- Run the script below
- For each user you need to fix, create the user at the server level (first Security folder in SSMS - not drilled down to a Db) with the same name as the orphaned user.
- Run the Associate Users script at the bottom to associate the two users
select dp.name [user_name] ,dp.type_desc [user_type] ,isnull(sp.name,'Orhphaned!') [login_name] ,sp.type_desc [login_type] ,dp.type ,dp.principal_id from sys.database_principals dp left join sys.server_principals sp on (dp.sid = sp.sid) where dp.type in ('S','U','G') and dp.principal_id > 4 order by sp.name --associate users --ALTER USER [my_user] WITH LOGIN = [my_user]