Servers have Logins, and databases have Users.
A Login and a User account are linked, sharing the same name and the same SID. Naturally, for each Login there can be many User accounts – one in each database.
Now, if you backup a database on one server and restore it onto another server. It may contain Users within that database, that do not have a corresponding Login on the second server.
Execute this command (against each user database) to list any orphaned Users …
SELECT DP.type_desc, DP.SID, DP.[name] UserName FROM [sys].[database_principals] DP LEFT JOIN [sys].[server_principals] SP ON DP.SID = SP.SID WHERE SP.SID IS NULL AND DP.authentication_type_desc = 'INSTANCE';
To fix this you can either remove the orphaned user account or create a matching login. Removing a user account is tricky to script as you need to remove individual attributes first.
Creating a login depends on the type, SQL or Windows. For SQL logins paste the name, SID, and password* into this command and execute it.
USE [master] GO CREATE LOGIN [SomeLogin] WITH PASSWORD = 'SomePassword', SID = 0xSomeSid;
(*If you do not know the SQL login password that was used on the old server then create a new one. They are not linked)
To create a Windows login use this command …
USE [master] GO CREATE LOGIN [SomeDomain\SomeLogin] FROM WINDOWS WITH DEFAULT_DATABASE = [master];