SQL Server User Mapping Error 15023

Question

I try to map my other DB to a user by going to
Security > Logins > right click someuser > Properties > User Mapping > Select DB > set as db_owner and then ok, but I keep on getting an error saying

User, group, or role 'someuser' already exists in the current database. (Microsoft SQL Server, Error: 15023)

What is causing the error, and how do I map that user to the database?

Answer 1

To fix the user and login mapping you need to open a query window in the SQL Server Management Studio. Enter the following two lines and replace myDB with the database name and myUser with the correct user name:

USE myDB

EXEC sp_change_users_login 'Auto_Fix', 'myUser'

If run successfully you should get an output like this one:

The row for user '****' will be fixed by updating its login link to a login already in existence.

The number of orphaned users fixed by updating users was 1.

The number of orphaned users fixed by adding new logins and then updating users was 0.**

Your user should now be mapped correctly.

Edit:

New way to Resolve/Fix an Orphaned User:

In the master database, use the CREATE LOGIN statement with the SID option to recreate a missing login, providing the SID of the database user.

CREATE LOGIN <login_name>   
WITH PASSWORD = '<use_a_strong_password_here>',  
SID = <SID>;  

To map an orphaned user to a login which already exists in master, execute the ALTER USER statement in the user database, specifying the login name.

ALTER USER <user_name> WITH Login = <login_name>;  

When you recreate a missing login, the user can access the database using the password provided. Then the user can alter the password of the login account by using the ALTER LOGIN statement.

ALTER LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';  

Answer 2

if it is just one or two users, then easiest way is to drop the database user from the restored database, remap the database user to the server login using SSMS. If the server login does not exist then just create it, map the user.

Option 2: If you are migrating a large number of users, use sp_help_revlogin. sp_help_revlogin is a Microsoft supplied stored procedure that will help migrate logins from one server to another, including passwords and SIDs. Here is a good article about it SP_HELP_REVLOGIN : http://www.databasejournal.com/features/mssql/article.php/2228611/Migrating-Logins-from-One-SQL-Server-to-Another.htm

Code patches to help use it : run following T-SQL Query in Query Analyzer. This will return all the existing users in database in result pan.

USE YourDB
GO
EXEC sp_change_users_login 'Report'
GO

Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Auto_Fix’ attribute will create the user in SQL Server instance if it does not exist. In following example ‘ColdFusion’ is UserName, ‘cf’ is Password. Auto-Fix links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins.

USE YourDB
GO
EXEC sp_change_users_login 'Auto_Fix', 'ColdFusion', NULL, 'cf'
GO

Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Update_One’ links the specified user in the current database to login. login must already exist. user and login must be specified. password must be NULL or not specified

USE YourDB
GO
EXEC sp_change_users_login 'update_one', 'ColdFusion', 'ColdFusion'
GO

2) If login account has permission to drop other users, run following T-SQL in Query Analyzer. This will drop the user.

USE YourDB
GO
EXEC sp_dropuser 'ColdFusion'
GO

Create the same user again in the database without any error.

Answer 3

If you assign permissions to a database user without mapping it to the database first, it throws the error you mentioned.

You should be able to delete the user, map it to the database and then assign the user to the db_owner role.