如何处理“只能对映射到Windows或SQL登录名的用户进行重新映射"； [英] how to deal with "remapping can only be done for users that were mapped to Windows or SQL logins"

问题描述

场景:

尝试从SQL Azure中获取的bacpac还原.

Trying to restore from a bacpac taken from SQL Azure.

可以是新的SQL Azure数据库实例，也可以是本地服务器.对于早期版本的管理门户网站或 DAC框架客户端工具.

Either to a new SQL Azure database instance, or an on premises server. For the earlier with the Management portal or the DAC Framework Client Side Tools.

这似乎工作正常，并且还原后自然不会将SQL用户映射到SQL登录名.

It seems to work fine, and naturally the SQL users are not mapped to SQL logins after the restore.

我尝试过的事情:

当我尝试将其映射为: alter user MyUser with login = MyLogin，它失败并显示:

When I try to map it with: alter user MyUser with login = MyLogin, it fails with:

消息33016，级别16，状态1，第6行无法将用户重新映射为登录名.只有映射到Windows或SQL登录名的用户才能进行重新映射.

Msg 33016, Level 16, State 1, Line 6 The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.

运行select * from sys.database_principals确实会列出用户，但是SID的长度比我创建的SQL身份验证用户要长得多.

Running select * from sys.database_principals does list the users, but with a much longer SID than a SQL authenticated user I created to compare it to.

在前提条件下，如果我运行sp_change_users_login 'Report'，则将未列出用户，因此不会将其检测为孤立.

On premises if I run a sp_change_users_login 'Report' the users are Not listed, so are not being detected as orphaned.

在前提下，如果我尝试使用 sp_change_users_login ，它将失败，并显示以下信息:

On premises if I try using sp_change_users_login, it fails with:

消息15291，级别16，状态1，过程sp_change_users_login，第114行终止此过程.用户名"MyUser"不存在或无效.

Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114 Terminating this procedure. The User name 'MyUser' is absent or invalid.

在前提下，如果我通过登录属性" UI的用户映射"部分进行尝试，则会得到:

On premises if I try it through the User Mapping section of the Login Properties UI, I get:

为用户我的用户"创建失败. ...用户，组或角色"MyUser" 当前数据库中已经存在.

Create failed for User 'My User'. ... User, group, or role 'MyUser' already exists in the current database.

我尝试重新做一遍，以防恢复时损坏了出于某些原因，结果相同.

I tried doing it all over again, in case something was corrupted when restoring for some reason, same results.

问题:

如何重新映射这些SQL用户?

How can I remap these SQL Users?

我想避免不得不从头开始重新创建那些对象以及它们与数据库中的架构对象之间的任何关系?

I'd like to avoid having to re-create those from scratch and any relation those have to the schema objects in the database?

一些其他信息:

一种与我为SQL Azure用户所见非常相似的SQL用户类型是使用

One type of SQL users that look a lot like what I'm seeing for the SQL Azure users, are ones created with

create user AnotherUser without login

在我上面使用的所有3种映射方法中，这些方法都以完全相同的方式失败.对于常规SQL用户，在任何方法中均不是这种情况.另外，sid也很长，并且以相同的"0x010500000000000903000000"开头

Those fail in the exact same way in all the 3 mapping approaches I used above. That is not the case in any of the approaches for regular SQL users. Additionally the sid is also long, and begins with the same "0x010500000000000903000000"

推荐答案

如果您得到了，则最有可能是损坏的备份.就像前提方案上的链接一样，该行为无处不在，并且可以追溯到备份损坏.

If you ever get that, it is Most likely a corrupt backup. Just like the linked on premises scenario, the behaviour comes out of nowhere and was traced to a corrupt backup.

就我而言，这是相同的.在发布问题之前，我的最后尝试是:数据库的另一种备份，它可以正常工作.

In my case, it was the same. Right before posting the question, my last try was: a different backup of the database, it worked without issues.

这篇关于如何处理“只能对映射到Windows或SQL登录名的用户进行重新映射"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！