 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / March 2008Why are logins disabled after 2k to 2k5 migration?
Hi, I did a SQL 2k to 2k5 migration using the detach/attach method. The migration went successful. After the migration the SQL logins were disabled. The password complexity was enabled in 2k5. I unchecked password complexity and enabled the SQL login, however I am still unable to log (Error: 18546, Sev:14, State: 8). This indicates password mismatch, however I know the password is typed in correctly. This is happening for every SQL login. After the SQL login password has been reset I am able to login in fine. Is this a know problem? Thanks, Jay Correction: The error is 18456 and not 18546 as stated below. > Hi, > [quoted text clipped - 10 lines] > Thanks, > Jay Hi What do you mean by detach/attach method? If this is different instance of SQL 2005 and not an in-situ upgrade I would not expect sids for the logins to match. Databases move between instances may have orphaned users see http://support.microsoft.com/kb/240872/EN-US/ John > Correction: The error is 18456 and not 18546 as stated below. > [quoted text clipped - 16 lines] >> Thanks, >> Jay I created an instance of SQL 2k5 on a seperate server and then migrated the 2K user databases by using detach/attach method. Before migrating the user databases, I transferred the logins by copying the database which was the default database for all user accounts. I used the sql 2k5 copy database wizard. > Hi > [quoted text clipped - 24 lines] > >> Thanks, > >> Jay Hi Jay This is from the "Using the Copy Database Wizard " topic in books online: "Transferred logins that were created by using SQL Server authentication must be enabled and have passwords reset on the destination server." John > I created an instance of SQL 2k5 on a seperate server and then migrated the > 2K user databases by using detach/attach method. Before migrating the user [quoted text clipped - 30 lines] > > >> Thanks, > > >> Jay Jay S I have been recently migrated many databases to SS2005 and have never seen this error. How dod you move logins? Do you have SP2 installed on the box to map users with logins? > Hi, > [quoted text clipped - 13 lines] > Thanks, > Jay I transferred the logins by copying the database which was the default database for all user accounts. All the logins in the instance had acess to this database. I used the sql 2k5 copy database wizard to copy this database (and the associated logins). > Jay S > I have been recently migrated many databases to SS2005 and have never seen [quoted text clipped - 19 lines] > > Thanks, > > Jay > I transferred the logins by copying the database which was the default > database for all user accounts. All the logins in the instance had acess to [quoted text clipped - 24 lines] > > > Thanks, > > > Jay you need to fix the users, you can't transfer them from one server to another. something like this should work, although I don't have a DB handy to test you will get the idea... EXEC sp_change_users_login 'Report' this should give you a list of broken users, then for each user run the following EXEC sp_change_users_login 'Auto_Fix', '<Login>', NULL, '<Password>' this should fix them, and hopefully your issue |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.