Problem with Orphaned Users/Logins in SQL Server 2005 Migration

Scenario:

You’ve run sp_detach_db on SQL Server 2000 and sp_attach_db on SQL Server 2005. Your database is now in 2005 and your application has now been reconfigured to point to the new server… but your application can’t log in.

Create the Login in SQL Server 2005. Logins and Users are different things. Logins apply to the server level, where users apply to the database level. One must have a LOGIN before having a USER.

Use the same password as your application. What’s happened is when you detached the USERS for your database were mapped to a LOGIN (via an ID) on your old server. You may have User(LoginID=1000) but the new login you made has an ID of 2000. Your user is an orphan. Let’s reunite them.

Run this T-SQL:

use databasename;
exec sp_change_users_login
	@action = 'update_one',
	@UserNamePattern = 'username',
	@loginname='loginname',
	@password='pass'

---

Possibly related posts: (automatically generated)

• Showdown: MySQL vs. SQL Server 2005 (Backups Edition)
• Re: Is SQL Server ‘keeping up’ with MySQL?
• Upgrading Sharepoint 3.0 to SQL 2005 Backend
• Follow up on Issue: Server principal is not able to access the database und…