Erro message 15023

[TxMelo]

I restored a database and now it will not allow me to add the users I need to. I receive the error message:
Error 15023: User or role 'username' already exists in the current database.
When I look in users, the only one there is dbo. I've been to security to add the database and I get the same message. I've deleted the user out of security, added it again, and I get the same message. Any suggestions???

 

[Bernadette]

Hi TxMelo
This problem is caused by orphaned users. SQL Server logins are mapped to a loginID not a name and when you move a database to another server the internal identifiers dont match any login on the target server.
This is what I do to fix it although I am sure there may be another way.

1. Right Click on your Server and select Properties
2. Under Server settings check the box to allow
modification of catalogs (system tables)
2. Open up Query Analyser
3. Delete the relevant logins from the syslogins table in
the master database.
4. Delete the relevant users from the sysuers table in the
user database.
5. Re-Add the Logins to the database (from a script if
possible)
6. Re-Add the users and roles and permissions to the
database (from a script if possible)
7. The users and logins are now correctly in the database.
8. Right Click on your Server and select Properties
7. Under Server settings un-check the box to prevent
modification of catalogs (system tables)

It is interesting to note that "Roles" do not loose their permissions when moving a databse to a new server. All you have to do is

1. Delete the relevant logins from the syslogins table in
the master database.
2. Delete the relevant users from the sysuers table in the
user database.
3. Re-Add the Logins to the database (from a script if
possible)
4. Re-Add the users to the database (from a script if
possible)
5. Add the users back into the "Role"(from a script if
possible)

I hope this helps with your problem

Good Luck
Bernadette