Re-associate Microsoft SQL Login with a Database User
I've had to search for this several times in the last few weeks, (ever since Mark Drew pulled me into the dark side that is local development.) I figured if I blog it, I'll always have the answer at hand.
If you are restoring a Microsoft SQL database from a backup onto a new server, and you need to re-associate the server login name to the database username, you need to call the "sp_change_users_login" stored procedure. That will allow you to reconnect them.
Comments
Another option i just read about was if you create a user with a known SID (i believe it's an option of the CREATE LOGIN or sp_add_login procedures in SQL 2005 and SQL 2000 respectively).
That way you can re-create the login on the new SQL Server with the right SID and SQL will automatically associate the Login with the DB user.
Not as useful in a DR scenario; the sp_change_users_login procedure is still your best bet.
Posted by: JohnEric at June 27, 2007 1:40 PM