SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error 15023: “User already exists in current database”


Error 15023: “User already exists in current database”

Author
Message
S Hodkinson
S Hodkinson
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6233 Visits: 1023

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
Greg Charles
Greg Charles
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38285 Visits: 6271

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
mimorr
mimorr
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3386 Visits: 52
I use:

sp_changeobjectowner '[domain\user].objectname' , 'dbo'

Looking in Enterprise Manager, should tell you what objects are owned by whom.



Michelle
Antares686
Antares686
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: Moderators
Points: 94782 Visits: 803

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





S Hodkinson
S Hodkinson
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6233 Visits: 1023

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
Eoin The Forge
Eoin The Forge
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2006 Visits: 804

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





Antares686
Antares686
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: Moderators
Points: 94782 Visits: 803
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.



Mahendra ranaweera
Mahendra ranaweera
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 6

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


Sugesh Kumar
Sugesh Kumar
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24217 Visits: 358

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
Mahendra ranaweera
Mahendra ranaweera
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 6

Hi Kumar

Yes this works. Thanks a lot.

Regards

Mahendra


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search