SQL Server“EXECUTE AS”出错

I've got the following setup:

我有以下设置：

There is a SQL Server DB with several tables that have triggers set on them (that collect history data). These triggers are CLR stored procedures with EXECUTE AS 'HistoryUser'. The HistoryUser user is a simple user in the database without a login. It has enough permissions to read from all tables and write to the history table.

有一个SQL Server数据库，其中有几个表设置了触发器（收集历史数据）。这些触发器是带有EXECUTE AS'StudusUser'的CLR存储过程。 HistoryUser用户是数据库中的简单用户，无需登录。它具有足够的权限来读取所有表并写入历史表。

When I backup the DB and then restore it to another machine (Virtual Machine in this case, but it does not matter), the triggers don't work anymore. In fact, no impersonation for the user works anymore. Even a simple statement such as this

当我备份数据库然后将其还原到另一台机器（在这种情况下是虚拟机，但无关紧要）时，触发器不再起作用。事实上，没有模仿用户的工作。甚至像这样简单的陈述

exec ('select 3') as user='HistoryUser'

produces an error:

产生错误：

Cannot execute as the database principal because the principal "HistoryUser" does not exist, this type of principal cannot be impersonated, or you do not have permission.

无法作为数据库主体执行，因为主体“HistoryUser”不存在，此类主体不能被模拟，或者您没有权限。

I read in MSDN that this can occur if the DB owner is a domain user, but it isn't. And even if I change it to anything else (their recommended solution) this problem remains.

我在MSDN中读到，如果数据库所有者是域用户，则可能会发生这种情况，但事实并非如此。即使我将其改为其他任何东西（他们推荐的解决方案），这个问题仍然存在。

If I create another user without login, I can use it for impersonation just fine. That is, this works just fine:

如果我创建另一个没有登录的用户，我可以使用它进行模拟就好了。也就是说，这很好用：

create user TestUser without login
go
exec ('select 3') as user='TestUser'

I do not want to recreate all those triggers, so is there any way how I can make the existing HistoryUser work?

我不想重新创建所有这些触发器，所以有什么方法可以使现有的HistoryUser工作吗？

4 个解决方案

#1

Detect Orphaned Users, then resolve by linking to a login.

检测孤立用户，然后通过链接到登录来解决。

DETECT:

USE <database_name>;
GO;
sp_change_users_login @Action='Report';
GO;

DETECT：USE ;走; sp_change_users_login @ Action ='Report';走;

RESOLVE:
The following command relinks the server login account specified by <login_name> with the database user specified by <database_user>:

USE <database_name>;
GO
sp_change_users_login @Action='update_one',
@UserNamePattern='<database_user>',
@LoginName='<login_name>';
GO

解决方案：以下命令将指定的服务器登录帐户与指定的数据库用户重新链接：USE ; GO sp_change_users_login @ Action ='update_one'，@ UserNamePattern =' '，@ LoginName =' ';走

https://msdn.microsoft.com/en-us/library/ms175475.aspx

#2

What user account does the trigger execute as.

触发器执行的用户帐户是什么。

You'll need to grant that user IMPERSONATE priviledges for the User Account HistoryUser.

您需要为用户帐户HistoryUser授予该用户IMPERSONATE特权。

 GRANT IMPERSONATE ON USER:: YourUser TO HistoryUser

More details here

#3

Problems like this that arise after moving a database from one machine to another usually involve mismatched SID's, although I'm not sure if or how it applies to your case. Try dropping and re-creating the database user, making sure to reinstate its permissions to those tables.

将数据库从一台机器移动到另一台机器后出现的这类问题通常涉及不匹配的SID，尽管我不确定它是否或如何适用于您的案例。尝试删除并重新创建数据库用户，确保恢复其对这些表的权限。

#4

It is an "orphaned user". It wont work. Documentation states this clear. :-( Fix "orphaned user" state and it will work again

这是一个“孤儿用户”。它不会工作。文件说明了这一点。 :-(修复“孤立用户”状态，它将再次工作

#1