Backing up a SQL Server database and restoring it to another server is a common operation done for reporting, testing, or development. When restoring on a different machine, a common problem is the login principals don’t actually correlate to the restored database users. This is because they are linked by an SID. This SID isn’t the user name, its some hash value, probably a GUID. The problem is fully described in this article.
Fixing the problem is really simple. Just run the following SQL and substitute and with the appropriate values. I added these lines to all my Powershell scripts that are used to restore a database and it works like a charm.
USE <database_name>;
GO
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>';
GO