| View previous topic :: View next topic |
| Author |
Message |
Mike Barskey
Joined: 10 Jul 2003
Posts: 124
Location: Seattle Technology Group, Inc. (Palm Springs office)
|
 Posted: Fri Apr 02, 2004 9:32 am Post subject: SQL login problems after a backup/restore |
 |
|
After doing a database backup and restore, you might encounter a problem where you cannot assign a user to a role or to the database, seeing an error such as:
"Error 15023: User or role '%s' already exist in the database"
To fix this problem, run the following SQL commands:
| Code: | | Exec sp_change_users_login @Action = 'Auto_Fix', @UserNamePattern = 'tsmith' |
Note that you will need to enter the command "use CP" (or "CP2002," or whaterver your DB is called) before the line above if you are executing these SQL commands from OSQL or Query Analyzer, or any environment other that CP's Interactive SQL Wizard.
After the users are "fixed" then you need to assign them to their roles.
Here is an explanation of what has happened to cause this error. When you restored the databases to the second server, the users in them remained intact. However, the users are pointing to logins that do not exist or exist, but have different SIDs. Thus the users in the databases are "inconsistent" or "orphaned" and are not visible except if you query directly the sysusers table.
SQL Server offers a nice stored procedure, which resolves this problem - sp_change_users_login. What it basically does is if the login already exists, the user is modified in the sysusers table to match the right SID. If the login does not exist, it is created automatically and appropriate rights to the database are granted.
- Mike |
|
| Back to top |
|
 |
Mike Barskey
Joined: 10 Jul 2003
Posts: 124
Location: Seattle Technology Group, Inc. (Palm Springs office)
|
| Posted: Fri Jul 09, 2004 10:51 am Post subject: Password problems after fixing users |
|
|
I have seen situations where fixing the login does indeed allow the user to login to SQL Server again, but it has reset their password to Null. In such a case, you will also need to issue a one-line command to update their password.
For detailed instructions, see the thread called "Changing users' passwords" in the the "IT (Technical Topics)" forum.
- Mike
Last edited by Mike Barskey on Wed Apr 11, 2007 8:02 am; edited 1 time in total |
|
| Back to top |
|
|
nbecker
Joined: 09 Aug 2003
Posts: 27
Location: Seattle, WA
|
| Posted: Wed Jan 12, 2005 12:20 pm Post subject: Second Solution |
|
|
Another solution to the above issue is to delete all the users from the users tree using Enterprise Manager for MS-SQL. Once you have deleted all user you then need to run the CPSQLUserMngUtil.exe utility that STG provides. You should notice that all the user will be added to the database without errors. Login to CP with a few different users to ensure this fixed the problem.
_________________
--------------------------
Noah Becker
(206) 324-4492 x5
-------------------------- |
|
| Back to top |
|
|
Ryan
Site Admin
Joined: 29 Oct 2002
Posts: 63
|
| Posted: Fri Jun 17, 2005 8:12 am Post subject: |
|
|
| I've had a situation where the customer had multiple DBs on the SQL Server. We tried deleting all users from all roles in all databases. The SQL User Manager continued to fail (with message "user is already in the database"). We determined that the users were in the "public" role. Enterprise Manager would not allow deleting these users (it was greyed out). So...we backuped up the database, restored it on my computer, deleted the logins, backup it up, and restored it on the server. Worked fine. I'm not sure whether it is necessary to delete all of the DBs or not. We did and it was successful. Perhaps it would have failed if we did not. |
|
| Back to top |
|
|
Mike Barskey
Joined: 10 Jul 2003
Posts: 124
Location: Seattle Technology Group, Inc. (Palm Springs office)
|
| Posted: Fri Feb 16, 2007 4:52 pm Post subject: Another way to fix user/login problems |
|
|
Thanks to Alex at Carnegie Mellon University for finding these helpful Microsoft KnowledgeBase articles:
- Mike |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
