SQL Server以用户身份执行过程 [英] SQL Server execute procedure as user

问题描述

我有一个存储过程，可以将数据加载到目标表中，并且在使用AD凭据的会话中运行时可以正常工作。

I have a stored procedure which loads data into a target table and works fine when run in a session with my AD credentials.

当我尝试在其中运行时一项工作（再次在运行方式选项中显示我的AD详细信息）突然导致登录名无法访问其中一个数据库。

When I try to run it in a job (again with my AD details in the Run As option) suddenly the login does not have access to one of the DB's.

我使用了

EXEC SP1

其中工作正常。

我用来模拟工作中存储过程的运行

I used (to emulate running the stored procedure in a job)

EXECUTE AS user = 'Domain\JDoe'

EXECUTE SP1

REVERT

哪个失败。

为什么使用相同的凭据运行存储过程会失败，而在不同的环境中成功使用了相同的凭据会话窗口？

Why does the stored procedure fail when running with the same credentials which are used successfully in a different session window?

预先感谢

推荐答案

您需要设置源数据库到 TRUS TWORTHY 。请注意，这还有其他安全隐患（请参见下文）。

You need to set the source database to TRUSTWORTHY. Note that this has other security implications (see below).

在SQL Server中，默认情况下，您不能使用假定的安全上下文从一个数据库移入另一个数据库，除非源是受信任的。将数据库设置为TRUSTWORTHY可以表明数据库是受信任的来源。这是一种安全措施，旨在防止某个人（通常是通过Injection）从某个应用程序侵入一个数据库，然后将其用作跳入同一SQL Server中所有其他数据库的跳板。通过将其设置为TRUSTWORTHY，您会说 该数据库是 安全 ，没有人能找出谁不应该这么做。

By default in SQL Server you cannot use an assumed security context to get out of one database and into another unless the source is trusted. Setting a database to TRUSTWORTHY is how you indicate that the database is a trusted source. This is a security measure designed to prevent someone who hacks into one database from an application (via Injection, usually) from then using that as a springboard into all of the other databases in the same SQL Server. By setting it to TRUSTWORTHY you are saying "this database is secure and no one can get out who isn't supposed to."

更改这样的数据库语句要求在您更改数据库语句时，没有其他语句在数据库中。您可以在命令末尾添加WITH ROLLBACK IMMEDIATE，以将其他所有人都扔掉。当然，这可能会产生自己的后果...;-）

Alter database statements like this one require that no one else is in the database when you ALTER it. You can add WITH ROLLBACK IMMEDIATE to the end of the command to throw everyone else out first. Of course that may have consequences of its own ... ;-)

这篇关于SQL Server以用户身份执行过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！