Associate SQL Server user with login after db restore for SQL Server

This post was borrowed from Computer Cabal and expanded upon:

After you’ve restored a backed up SQL Server database instance you may find the user logins are no longer associated with the users. You can’t make this fix via SQL Server Management Studio but you can run the commands below to fix it. Note: It only works for SQL Server 2005 SP2 and later, so the first thing to do is check what version of SQL Server you have.

1. Log into the Management Studio for SQL Server 2005 run:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

• Or if you are using SQL Server 2008 run:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

• In the second column you should see a table with a row name you should see “SP2” or “SP3”, if you see something like “SP1” or “RTM” or don’t see a second column then you need to upgrade. Here’s the link to download SQL Server 2005 SP3.
• Once you’ve got at least SQL Server 2005 SP2 run:

alter user [user_name] with login=[login_name]  

• Now you should be all set.

For more information on How to Identify SQL Server versions and editions go here.