October 28, 2008 at 2:01 pm
Pesky 15023 error (user already exists)
I am moving databases from sql2000 to SQL2005. There are a number of SQL logins in each database which need to be recreated on the new server. I save the .bak file an load it into the new machine.
Following the instructions on KB 274188
1) EXEC sp_change_users_login 'report'
I get a list of the user that need to be matched.
2) For each user I then run:
EXEC sp_change_users_login 'Auto_Fix','User name',NULL,'password'
I get the expected response:
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 1.
At this point, all looks good. The user is created in the main users display, and the rights match between the newly recreated user login and the matching entry in the database login list. There is no ambiguity, as this is a new machine without existing users. All the imported databases are coming from the same server, so we don't need to worry about similarly named different users.
I thought everything was fine. However if, working in Management Studio, I try to edit one of the newly created users, if I make any change at all, I get:
User, group or role 'xxxx' already exists in the current database (microsoft SQL Server, Error 15023)
What am I missing?
...
-- FORTRAN manual for Xerox Computers --
October 28, 2008 at 2:35 pm
Try using UPDATE ONE instead of AUTO_FIX
October 28, 2008 at 2:47 pm
What you really should have done is to create the logins before restoring the databases. Google 'sp_helprevlogin' to find a procedure for copying logins from one server to another.
Make sure you use the one that is for copying from 2000 to 2005.
This will create the logins with the same SID's and passwords. When you restore your database, the users/logins will automatically synch up and you won't have any other issues.
My guess as to why you are seeing these errors is that the login you are editing was not synchronized with the user in the database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply