SQL Server 用户映射错误 15023
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40256032/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server User Mapping Error 15023
提问by JC Borlagdan
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
我尝试通过转到
安全 > 登录 > 右键单击某个用户> 属性 > 用户映射 > 选择数据库 > 设置为 db_owner 然后将我的其他数据库映射到用户，但我一直收到错误消息
User, group, or role 'someuser' already exists in the current database. (Microsoft SQL Server, Error: 15023)
当前数据库中已存在用户、组或角色“someuser”。（Microsoft SQL Server，错误：15023）
What is causing the error, and how do I map that user to the database?
导致错误的原因是什么，如何将该用户映射到数据库？
回答by CR241
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:
要修复用户和登录映射，您需要在 SQL Server Management Studio 中打开一个查询窗口。输入以下两行，并将 myDB 替换为数据库名称，将 myUser 替换为正确的用户名：
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.
在 master 数据库中，使用带有 SID 选项的 CREATE LOGIN 语句重新创建丢失的登录，提供数据库用户的 SID。
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.
要将孤立用户映射到已存在于 master 中的登录名，请在用户数据库中执行 ALTER USER 语句，指定登录名。
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 语句更改登录帐户的密码。
ALTER LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';
回答by NG.
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.
如果只有一两个用户，那么最简单的方法是从恢复的数据库中删除数据库用户，使用 SSMS 将数据库用户重新映射到服务器登录。如果服务器登录名不存在，则只需创建它，映射用户。
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
选项 2：如果要迁移大量用户，请使用 sp_help_revlogin。sp_help_revlogin 是 Microsoft 提供的存储过程，可帮助将登录名从一台服务器迁移到另一台服务器，包括密码和 SID。这是一篇关于它的好文章 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.
代码补丁以帮助使用它：在查询分析器中运行以下 T-SQL 查询。这将在结果盘中返回数据库中的所有现有用户。
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.
在查询分析器中运行以下 T-SQL 查询以将登录名与用户名关联。如果用户不存在，'Auto_Fix' 属性将在 SQL Server 实例中创建用户。在以下示例中，“ColdFusion”是用户名，“cf”是密码。自动修复将当前数据库中 sysusers 表中的用户条目链接到 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
在查询分析器中运行以下 T-SQL 查询以将登录名与用户名关联。'Update_One' 将当前数据库中的指定用户链接到登录。登录名必须已经存在。必须指定用户和登录名。密码必须为 NULL 或未指定
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.
2) 如果登录账号有删除其他用户的权限，在查询分析器中运行以下T-SQL。这将删除用户。
USE YourDB
GO
EXEC sp_dropuser 'ColdFusion'
GO
Create the same user again in the database without any error.
在数据库中再次创建相同的用户，没有任何错误。
回答by knockout
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.
您应该能够删除用户，将其映射到数据库，然后将该用户分配给 db_owner 角色。
回答by Yusuf ünlü
First drop your user, then execute the script below:
首先删除您的用户，然后执行以下脚本：
USE [YOURDB]
GO
CREATE USER [USERNAME] FOR LOGIN [USERNAME]
GO
USE [YOURDB]
GO
ALTER USER [USERNAME] WITH DEFAULT_SCHEMA=[dbo]
GO
