I had the pleasure of refreshing the development instance of the Harris County Master Gardener’s database this morning. Basically, Dev was getting too old to conduct testing on, so took the most recent Production backup (from last night), and using MS SSMS I was able to restore the database without much incident.
However, my troubles then began when I attempted to pull up the Development Web Site. The application couldn’t connect to the database. I’ve had this type of thing happen in the past, but as usual, I’ve forgotten how to fix it.
After some researching on the Web, I came up with a Microsoft Support Article entitled, Troubleshooting Orhpaned Users, and I was able to fix the problem with relative ease. Look under the section “Steps to Resolve Orphaned Users”.
The following are the steps I undertook:
1. Using MS SSMS, connect to the Sql Server instance where the copied database exists.
2. Open a New Query window and enter: Use DataB go sp_change_users_login ‘report’
3. The stored procedure will display output for all of the affected login’s
4. Open a New Query window and enter: Use DataB go sp_change_users_login ‘update_one’, ‘test’, ‘test’ for each login.
That should fix the problem!