Subject: SQL2000: Error 15023: User or role '%s' already exists in the current database
Problem Description:
===================
You backed up the user database on one of the SQL Server in production and restored it on to a SQL Server in test environment. When you try granting access to a particular login you were getting the following error:
Error 15023: User or role '%s' already exists in the current database
Cause:
===================
- User logon information is stored in the syslogins table in the master database.
- By changing servers, or by altering this information by rebuilding or restoring an old version of the master database, the information may be different from when the user database dump was created.
- If logons do not exist for the users, they will receive an error indicating "Login failed" while attempting to log on to the server.
- If the user logons do exist, but the SID values in master..syslogins and the sysusers table in the user database differ, the users may have different permissions than expected in the user database.
Resolution:
===================
- Run the sp_change_users_login stored procedure with the Report option. This will generate a list of users that will be altered.
EXEC sp_change_users_login 'Report'
- Run the sp_change_users_login stored procedure with the Auto_fix option to re-associate relationships between the syslogins, sysusers and sysalternates tables.
- This did not work and we had to use the sp_change_users_login stored procedure with "update_one" option which linked the specified user in the current database to login. This fixed the problem
- We checked to ensure that the affected users have the appropriate permissions
ADDITIONAL INFORMATION:
=======================
You can refer these KB articles for additional information:
KB168001: User logons and permissions on a database may be incorrect after the database is restored
http://support.microsoft.com/kb/q168001/
KB246133: How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/kb/q246133/
==========================================================================
My Method:
Go to the problematic database and run the following script:
Select * from sysusers
It shows all user in the current database
2. Run the following scripts to show the user list which have no login info in the MASTER..syslogins. It is the root cause for the issue: the user info in database has no map relationship with the login info in master..syslogins
EXEC sp_change_users_login 'Report'
3. Create the login with the same name in the above step2 list
4. Run the following script which linked the specified user in the current database to login. This fixed the problem
EXEC sp_change_users_login 'Update_One', 'username', 'loginname'