Error 21002 [SQL-DMO] User "xxx" already Exists

Question

Error 21002 [SQL-DMO] User "xxx" already Exists

Nick Bell

SSC Enthusiast

Points: 151
More actions
July 27, 2005 at 1:21 am

#88313

Hi There,
I have restored an existing Database from another source and it has loaded a User with it.
I have tried creating a login to match this User but it won't allow me to add the permissions to this login. I can't delete the user from the Database.
Any idea how I can delete this user from the Database?
Can I use query analyser in anyway?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 1

To delete the user from the sysusers table you will need to first either through EM or QA set the property to allow you to make changes directly to the system catalog. Once you have done this you should be able to open the table in either EM or QA and delete that user directly from there.

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Nick Bell SSC Enthusiast Points: 151 More actions · Answer 2

What is the Command I need to use in QA to allow changes to be made to the system Catalog?

Martin Mojzisek SSCrazy Points: 2207 More actions · Answer 3

Don't bother yourself with changes to system catalog, it's not necessary in this case. Your problem is called orphaned user - mismatch between login and user in system tables. You can use this script to resolve it:

use specify_your_db_name

DECLARE @UserName nvarchar(255)

DECLARE orphanuser_cur cursor for

SELECT UserName = name

FROM sysusers

WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null

ORDER BY name

OPEN orphanuser_cur

FETCH NEXT FROM orphanuser_cur INTO @UserName

WHILE (@@fetch_status = 0)

BEGIN

PRINT @UserName + ' user name being resynced'

EXEC sp_change_users_login 'Update_one', @UserName, @UserName

FETCH NEXT FROM orphanuser_cur INTO @UserName

END

CLOSE orphanuser_cur

DEALLOCATE orphanuser_cur

go

It should synchronize the user and the login (names must be the same for this to work). Existing permissions for the db user are kept as well.

Mark Catoe SSC Enthusiast Points: 156 More actions · Answer 4

What happens if the the user OWNS some objects? It will break the script until until you correct the issue of object ownership. How do you automate the removal of ownership by the orphan user(s)??

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

Off-hand:

You build a cursor of orphaned users.

Within that you build a cursor of objects owned by the particular user.

You use sp_helprotect to output the permissions to a temp table.

You execute a change owner on the object.

You reapply permissions (using a cursor) on the object.

Close object cursor loop.

Drop orphaned user.

Close orphaned user cursor loop.

K. Brian Kelley
@kbriankelley

Martin Mojzisek SSCrazy Points: 2207 More actions · Answer 6

IMHO, you shouldn't run into problems with object ownerwhip using sp_change_users_login proc at all. Object owner is identified by user id in sysobjects table which doesn't change during this process. This is SIDs mismatch issue.

Henry F. Arnold SSC Rookie Points: 41 More actions · Answer 7

I tried several propsed solutions, including the ones here and found that I wasn't able to get any scripts to run or if they did run, not result in what I needed. I found that what worked for me was to log into EM with "sa" and expand the tree for database we use. All the userids showed up. I deleted one and was then able to go into Security and create the login I was interested in. I'm fortunate that I only have 12 current logins to recreate....

Thanks,

Hendry Sudjatmiko SSC Rookie Points: 34 More actions · Answer 8

Hi Martin,

I found your suggestion works for me. It is a big help.

Thanks for posting the solution