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]