Troubleshooting
Problem
I.T. administrator DBA backups Controller database from SQL server #1. DBA restores Controller database onto different SQL server (server#2). DBA runs the 'update user.sql' script (on server#2) to remove orphaned users: [
EXECUTE sp_change_users_login 'Update_One', 'fastnet', 'fastnet' GO] DBA receives error message.
Symptom
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 131
Terminating this procedure. The Login name 'fastnet' is absent or
invalid.
Cause
Scenario #1
DBA is using the wrong name (e.g. 'fastnet') for their SQL login. For example, perhaps it should be 'cognos' or 'controller'.
Scenario #2
DBA has not created the required SQL login before running the script.
Scenario #3
DBA is accidentally running the script on the wrong database (e.g. 'master', not 'controllertest' etc.).
Resolving The Problem
Scenario #1
Browse the SQL database tables, to find out the name (e.g. 'cognos') of the owner of them. Modify the script accordingly.
For example:
EXECUTE sp_change_users_login 'Update_One', 'cognos', 'cognos'
GO
Scenario #2
Create the *same* (as server #1) named SQL login on the target (new) SQL server (e.g. server#2). Then re-run the script.
Scenario #3
Select the correct Controller database, before re-running the script.
Steps:TIP: See attached document (Printscreen_showing_Database_table_owner.jpg) for an example of the concept.Scenario #1
For SQL 2005:
- Launch SQL Server Management Studio
- Expand <server> - 'databases'
- Locate new Controller database
- Expand this database, and expand Tables
- Check the name of each of the (non-system) tables (e.g. fastnet.CDARCRES and fastnet.crdAccountReports...). They will begin with the same SQL login name (e.g. controller)
- Modify the script so that both of the end values are this name (e.g. EXECUTE sp_change_users_login 'Update_One', 'controller', 'controller')
- Re-run script
Scenario #2
For SQL 2005:
- Launch SQL Server Management Studio
- Expand <server> - 'databases'
- Locate new Controller database
- Expand this database, and expand Tables
- Check the name of each of the (non-system) tables (e.g. fastnet.CDARCRES and fastnet.crdAccountReports...). They will begin with the same SQL login name (e.g. controller)
- Expand <server> - 'Security' \ logins
- Create a new login (e.g. 'controller') as per official documentation
- Modify the script so that both of the end values are this name(e.g. EXECUTE sp_change_users_login 'Update_One', 'controller', 'controller')
- Re-run script
Scenario #3
Re-run the script on the correct database.
Related Information
Historical Number
1039309
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21347799