none
Ophaned Logins after restore - Exec sp_change_users_login - The Login name is absent or invalid

    Question

  •  

    Did the restore. made sure I could access the new db with sa.. no
    problem.


    See some orphan logins that don't work (expected)..


    did this to report them:

     

    Exec sp_change_users_login 'REPORT'

     

    two reported.

     

    did this on one of them:


     

    Exec sp_change_users_login 'Update_One', ‘xxxx’, 'xxxx'

     

    But got error:

    Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line
    131 Terminating this procedure. The Login name 'xxxx' is absent or
    invalid.

     

    Also, There are users that access the old DB with their Windows User
    name or AD name. I don't see them in the user access list for the db
    and can't seem to map them to some of the AD groups that have access.

     

    Is there any way to report exactly with what AD group a user was able
    to access a DB? There isn't any setting that would allow everybody in
    a domain to access a db without an AD group right?

     

    Thanks for any help or information.

    Tuesday, June 03, 2008 4:19 PM

Answers

  • It sounds to me like you need to create the SQL Server Login that the database users maps to by SID.  Did you move servers, or are you recovering from a crash?  If you are recovering from a crash then you need to restore the master database since it holds all your Login information.  Then you can map your database users with sp_change_users_login.  If you are just moving the database to another server, you can script your logins, and recreate them on the new server with the scripts on the following Example Article:

     

    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=TransferLogins&referringTitle=Home

     

    Tuesday, June 03, 2008 8:03 PM

All replies

  • It sounds to me like you need to create the SQL Server Login that the database users maps to by SID.  Did you move servers, or are you recovering from a crash?  If you are recovering from a crash then you need to restore the master database since it holds all your Login information.  Then you can map your database users with sp_change_users_login.  If you are just moving the database to another server, you can script your logins, and recreate them on the new server with the scripts on the following Example Article:

     

    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=TransferLogins&referringTitle=Home

     

    Tuesday, June 03, 2008 8:03 PM
  • Hi I am getting the same thing. Did you get this resolved? I followed the direction from Jonathan's post but It's still not working for me. My situation is slightly different. I'm moving a DB from SQL 2000 to SQL 2008. The KB articles reference below does not have that scenario.

     

    Thanks,

     

    TKE402

    Thursday, June 24, 2010 3:18 PM
  • Did you figure this out Chris?
    Tuesday, May 17, 2011 1:51 AM