I used a backup of a production database to create a test environment and ended up with an orphaned user in the database. It’s always a good idea to restore from your backup files occassionally, just to be sure that they are functional, valid, and that you remember the process.
However, because I restored to a different server with a fresh (Ok, it’s SQL 2000 SP4; maybe clean would be a better word) install, the SQL Login didn’t exist that corresponds to a user that is part of the database.
I used the sp_change_users_login stored procedure to list the orphaned account and SID. Then I used sp_addlogin to create a login with the same name, password and SID in the master database. Now the user can query away!
I started with KB 314546, and then referenced the books online for sp_addlogin.
