Problem with Orphaned Users/Logins in SQL Server 2005 Migration
March 14, 2008
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'