User or Role Already exist?

[cceng]

I moved my databases over to a new server. Now when I add my user dba and try to assign it to the database I get the message "error 15023: User or role "dba" already exist in the current database.

What is the best way for me to resolve this?

 

[mfairchild]

I've found in the past, at least when restoring databases, that the backup will keep the user record in the sysusers table that is local to the database, however, the UID in the user database does not match the UID in the master database. When Enterprise Manager shows you the users in the database, your username will not appear (I believe because the join between the two sysuser tables is failing). When you go to re-add your user, to checks to see that the username does not exist. Unfortunatly, the user name does exist. An easy way to fix this is to delete the user record from the user database sysuser table. You'll have to change the server settings to allow user modifications to be made directly to the system catalogs though.

 

[cceng]

I appreciate thew quick response.

Unforunately I am new to SQl and not sure how to do what you just mentioned. If there is an easy way of explaining this I would appreciate it.

Thanks!

 

[tlbroadbent]

Read the article, "Fixing broken logins and transferring passwords," at SWYNK.COM for details on fixing logins when moving databases from one server to another.

http://www.swynk.com/friends/boyle/fixingbrokenlogins.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.