Error 15023: “User already exists in current database”
-
I've had to create a new test database using a restore of a live database. The people who will be doing the testing already exist as logins on this server but when I tried to give them access to the test database I got the above message.
I've got the note below as a resolution
1. Select SQL Query Analyser and run
exec sp_dropuser ‘user login’
2. If the user login is unable to be dropped due to ownership of objects, change the ownership of the objects to ‘sa’, then repeat step 1.
3. Recreate the user login.
How can I tell which user login owns which objects (nobody wrote that bit down)? If anyone could let me know, overnight, I'd be grateful. I've got to sort this out in the morning. Will this script work?
sp_changedbowner 'sa'
Madame Artois
-
Run this query in the database:
select u.name,o.name
from sysobjects o, sysusers u
where o.uid = u.uid
order by u.name
Greg
Greg
-
I use:
sp_changeobjectowner '[domain\user].objectname' , 'dbo'
Looking in Enterprise Manager, should tell you what objects are owned by whom.
Michelle -
The reason you are getting this is that the user I assume existed on the production system and already was granted access to the database. When you restore to another database the system will still show the User in the users of the database. However because of a difference in the sid value it doesn't sync them to each other.
Now when you try to grant the account access to the db by default it creates a user with the same name as the login and because that user already exists in the datbase it gives you the error.
The easiest way to resolve this is to sync the sid between the user of the restored database and the login of the test server.
To do this I commonly perform
EXEC sp_change_users_login 'AUTO_FIX', 'database username'
which will give you a statement about n Orphans founhd, n Orphans updated. Where n is the number of database users.
See SQL BOL for more information about sp_change_users_login
-
I ran this script with the user 'robertba' (a sys admin who left some time ago) and got the following result
The row for user 'robertba' will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.
However I still get the same error when I try to give 'robertba' access to the database.
Madame Artois
-
Once you've confirmed he doesn't own any more objects, you can delete him from the sysusers table on the relevant database
NB. You must allow ad hoc modifications to the server first. Make sure you change this back straight away.
Once that row is deleted you can add the user login correctly.
Eoin
-
You don't have to give access to the database now roberta already has access. In the database in the users group (folder, subtree or whatever you want to call it) you will see robertba alread has access, you might need to hit F5 if you were there already to get it to refresh.
-
Even after doing this user ownership with the objects does not seems to be fixed. Example when I login using this user I would have to say select user.table instead of just select table
-
run this command
sp_change_users_login 'report'
if the user lists in the output then run the following given below
sp_change_users_login 'update_one','username','username'
this should ix your problem.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Hi Kumar
Yes this works. Thanks a lot.
Regards
Mahendra
-
Always make this a point to run the script above after you restore a database from another server so that orphan ids gets fixed.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Best solution, it solved mine.
Thank you!
-
run the following command in the database with the login problem, this commands re-map the user's security identifier to match the sql server login's security identifier
ALTER USER user WITH LOGIN = serverlogin
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply