October 17, 2008 at 2:03 pm
I tried to migrate a database from 2000 to 2005. There are some user name in the 2000 database that did not have a logon name. When I tried to move to 2005, it requires me to have logon name. What should I do?
October 17, 2008 at 3:03 pm
Try sp_change_users_login
October 17, 2008 at 9:34 pm
this might come handy in the near future too..
-- ===================================================
/*
How to fix orphaned SQL Server users
--------------------------------------------------------------------------------
Summary
When you restore a Microsoft SQL Server database on a different machine, you cannot access the database until you fix the permissions.
--------------------------------------------------------------------------------
Detail
The problem is that the user in the database is an "orphan". This means that there is no login id or password associated with the user. This is true even if there is a login id that matches the user, since there is a GUID (called a SID in Microsoft-speak) that has to match as well.
This used to be a pain to fix, but currently (SQL Server 2000, SP3) there is a stored procedure that does the heavy lifting.
All of these instructions should be done as a database admin, with the restored database selected.
First, make sure that this is the problem. This will lists the orphaned users:
*/
EXEC sp_change_users_login 'Report'
/*
If you already have a login id and password for this user, fix it by doing:
*/
EXEC sp_change_users_login 'Auto_Fix', 'user'
/*
If you want to create a new login id and password for this user, fix it by doing:
*/
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'
Cheers,
John Esraelo
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply