Answered by:

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?

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:

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:

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.