Many times where you copy a databese from a SQL Server instance to another, some user get orphaned. This situations is because of user database exist (with the copy, export/import…) and the login doesn’t.
You can get a report of orphaned users in a database with the next stored procedure;
USE demodb GO sp_change_users_login @Action='Report' GO BHappy 0x769008D2CDC6694F9A2230A4786901C1 Financial 0x6FBF6620832EC54181A3520F9257EEC0 Financialweb 0xB5A86A3BDC7E5D44AD48AD931D15F88E
i) Case i login not exist on destinity;
From origin generate the create login script, the ouput is similiar to show below;
CREATE LOGIN [BHappy] WITH PASSWORD=N'Æa¦m²}Ù?2RêtÖ??w¯çbªR®+ä?xjëEg', DEFAULT_DATABASE=[Financial], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO
After that the user, you need link de user database to the new login;
EXEC sp_change_users_login 'Auto_Fix', 'BHappy'
The output is similar to this;
The row for user 'BHappy' will be fixed by updating its login link to a login already in existence. The number of orphaned users fixed by updating users was 1. The number of orphaned users fixed by adding new logins and then updating users was 0.
ii) Login exist in destinity:
Execute de next stored procedured to link both;
use demodb EXEC sp_change_users_login 'Auto_Fix', 'Financial'
HTH – Antonio NAVARRO