error 21002:[SQL-DMO]User 'xxx' already exists

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

error 21002:[SQL-DMO]User 'xxx' already exists

Author

Topic

jshurak
Starting Member

44 Posts

Posted - 2006-11-14 : 16:53:52

Hello everyone. This is my first post so I hope someone can help me out. After extensively google-ing this error message I have not had any luck resolving this issue. We restored our test server from our production server to test an upgrade. I created a login for a user so they could test, but when I try to give them access to specific databases I get this error: error 21002:[SQL-DMO]User 'xxx' already exists.

I have run this procedure with no luck:
SP_CHANGE_USERS_LOGIN UPDATE_ONE, 'username','login_name'

I have also tried this one:
sp_change_users_login AUTO_FIX, 'my_user'

I've manually dropped and added the user from the system tables but for some reason I can't get past this. I've also checked various links to Microsoft help pages!! AAAGGGHH!! Any help would be greatly appreciated. Thanks ahead of time!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-14 : 17:04:25

tried sp_revokedbaccess & sp_grantdbaccess ?

mr_mist
Grunnio

1870 Posts

Posted - 2006-11-15 : 05:03:09

sp_change_users_login AUTO_FIX, 'my_user'

This should work for a sql login assuming that the login name is the same as the user name. Is it a sql login?

-------
Moo. :)

jshurak
Starting Member

44 Posts

Posted - 2006-11-15 : 08:28:11

Thanks for the speedy responses. I haven't tried the sp_revokedbaccess & sp_grantdbaccess, I'll give them a try today. It is a SQL login. I have tried the sp_change_users_login AUTO_FIX, but to no avail. I get 'number of orphaned accounts fixed is 0' message.

The error occurs when I try to grant specific permissions to databases for the user.

jshurak
Starting Member

44 Posts

Posted - 2006-11-15 : 09:20:28

I just tried the revoke and grant statements with no luck.

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-11-15 : 11:23:14

Run this in the database in question:


select issqlrole, isapprole
from sysusers
where name = 'name of your user'

If you get a 1 in either, you are dealing with a role, instead of a user.

jshurak
Starting Member

44 Posts

Posted - 2006-11-15 : 11:26:36

Thanks for posting a response mcrowley! I ran the query you gave me and I got a 0 value returned

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-11-15 : 11:50:11

You got a pair of 0's with one row returned, right?

So the entry is in sysusers, but sp_dropuser has not removed it? Has sp_dropuser returned an error?

Also, if sp_change_users_login did not work, does the login exist?

jshurak
Starting Member

44 Posts

Posted - 2006-11-15 : 13:50:50

ok, the user did have a login. sp_drop user did not return an error. Most recently (5 minutes) I dropped the user from the database and deleted his login. I ran your original query and it returned no results(no record of this user in this db). I recreated the login and when I tried to give him assign him specific roles in certain databases I get the 'user already exists error.'

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-11-15 : 15:21:32

This is one of the many reasons Enterprise Manager has a (deservedly) bad reputation. Try it entirely from Query Analyzer:


exec sp_dropuser username
exec sp_adduser loginname, username
exec sp_addrolemember role1name, username
exec sp_addrolemember role2name, username
exec sp_addrolemember role3name, username
.
.
.

This will give you a script that can be run any number of times on any server, and should give you the same results each time. Provided all applicable roles and logins exist to start with.

jshurak
Starting Member

44 Posts

Posted - 2006-11-16 : 09:41:15

mcrowley,

Thank you for all your replies. I am certainly documenting your help because it will no doubt be extremely useful in the future. We learned that our issue was the result of the application for which the database was created. What we had to do to resolve the issue was run a script from the vendor. The script ran on our production server and cataloged all users and login information from said server. The output of the script was another script to be run on the new server, in our case the test server. The new script essentially deleted all the logins and users from the test server and created them again using the information from the production server.

Again thank you all for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources