asked
How to fix Orphan User Who did nt know password
Hi Recently I migrated 5 db from one server A to another server B After successful migration I checked in Server B for orphan users. List of orphan users 50(This New logins created in Server A but not Server B) I usually solve issue using see below EXEC sp_change_users_login 'Report' --Reports orphaned user EXEC sp_change_users_login 'Update_One', 'user', 'user' But It fix issues for already existing logins. But it throws following error on not existing logins as see below Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 131 Terminating this procedure. The Login name 'xxxx' is absent or invalid. When I browsed in internet and found following solution to create new login and fix orphan user but we have to provide password. EXEC sp_change_users_login 'Auto_Fix', 'jgordon', NULL, '123456' My Question is How to solve Issue without Provide password for orphan user of absent login? Note: Bcos We did nt ask password Every time from user of Server A of Orphan user.
Are these all SQL Server logins or are you using Windows logins?
Since you still have all the logins on ServerA, you just need to migrate them to ServerB while retaining the original passwords. Once they are in place on ServerB you can repair any that appear to be orphaned. To migrate your logins from ServerA to ServerB, follow the instructions that Microsoft have published for transferring logins between different SQL Server instances. You'll want to use Method 3 " Create a log in script that has a blank password" (it creates and uses sp_help_revlogin) - [
http://support.microsoft.com/kb/918992][1] It says "blank password" in the title, but step 3 (and previous experience) confirms that it actually migrates both the logins and passwords - "The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password." Once the logins are in place on ServerB, they should already be mapped because the script preserves the SID. If any of your users are still orphaned, try running the orphaned user scripts that you've already used. [1]:
http://support.microsoft.com/kb/918992
15 People are following this question.
