Authentication failed after restoring database

The last year or so, I’ve had a problem just about every time, I’ve restored a database that was backed up from another server.  After every restore, the applications I’ve been working with, throws an exception with authentications errors. The last project I worked on, we could solve this by deleting the user from the DB and add again.  A pretty weird solution, but it worked. But today I ran into the same problem, on another application, where I wasn’t able to delete the user for some reasons. So what to do? It would be nice to find the right solution to this topic, and not just the workaround I’ve been using previously.

The last few months I’ve had the pleasure of working with Erik Ejlskov, who knows a lot about MSSQL Server, so why not just ask him? As usual he was just about, to send a mail to everybody in the office with a solution to this.

If you get the failing authentication, after restoring a database, run the following sproc:

EXEC sp_change_users_login 'Update_One', '[username]', '[username]';

 I wonder what Update_One means, maybe Erik can give an answer to that tomorrow. Anyway it works, and I wish that I'd known this before, since it's really been bugging me.