Get Login failed for user after a database restore

If you are using SQL Server logins (not Windows Authentication) to connect to your database and you just restored a fresh copy of your database server (say from prod to dev server) you will likely get an error saying something like the following:

Login failed for user 'SomeUser'.

The reason is that your login and database user have become disconnected.

If are restoring your database to a database server that you have NOT had your database before, you will need to create the Login (not the database user because it should exist and by moved when you did your backup and restore) before you try this solution.

Once you create the login, you will need to link the database user and your new login. If you don’t, you may get an error like this:

Cannot open database "SomeUser" requested by the login. The login failed. Login failed for user 'SomeUser'.

You can link them with a simple T-SQL statement

sp_change_users_login 'Update_One', 'SomeUser', 'SomeUser'

The ‘Update_One’ string is important and is the same no matter what user you are doing. You can do all logins at one by changing the parameters to this stored procedure, but I am always too scared to do it. I like doing it one login at a time, since SQL Server will match automatically. I never really looked enough into what criteria it uses, so I don’t use that.

 MSDN has more details on this method if you need it.