SQL Server User Mapping Error 15023

SQL Server User Mapping Error 15023

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?

 

回答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.

USE Avii

EXEC sp_change_users_login 'Auto_Fix', 'avii'

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>';  

 

Linking ALL Users to Login after restoring a SQL Server 2005 database

Yes, you can do that by executing:

EXEC sp_change_users_login 'Auto_Fix' , 'TheUserName';

However if your question was can I fix all users automatically then this won't do that.

 

How to connect an existing SQL Server login to an existing SQL Server database user of same name

The new way (SQL 2008 onwards) is to use ALTER USER

ALTER USER OrphanUser WITH LOGIN = correctedLoginName;

 

Login和User

获取server login和database user

SELECT *
FROM master.sys.server_principals
WHERE type = 's';
SELECT *
FROM master.sys.database_principals
WHERE type = 's';

 

 

作者:Chuck Lu    GitHub