您可以将存储过程设置为始终以特定用户身份执行吗? [英] Can you set a stored procedure to always execute as a specific user?

问题描述

我想将存储过程设置为始终以特定域用户的身份执行.无论用户调用或尝试执行该过程，我是否可以强制以其他用户身份执行该过程.

I would like to set a stored procedure to always execute as a specific domain user. Regardless of the user calling or trying to execute the procedure, can I force the procedure to be executed as another user.

我们的一个供应商将他们的应用程序硬编码为使用本地数据库帐户来执行某些程序以导入一些 csv 文件.不幸的是，我们无法将 csv 文件存储在本地数据库服务器上，需要将它们放在网络共享上.因此，我需要将这些过程设置为作为域用户执行，该用户可以访问我将放置文件的网络共享.下面的语句授予执行权限，但我需要一些强制以特定用户身份执行 sp 的东西.

One of our vendors has their app hardcoded to use a local db account to execute certain procedures to import some csv files. Unfortunately we cannot be storing the csv files on the local database server and need to place them on a network share. Therefore I need to set these procedures to be executed as a domain user with access to the network share where I will be placing the files. The below statements grant the execute permissions but I need something that forces the sp to be executed as a specific user.

CREATE ROLE exec_procs
GRANT EXECUTE ON sys.sp_oaMETHOD TO [domain\user]

推荐答案

您想使用 EXECUTE AS 在 SP 定义中.

You want to use EXECUTE AS in the SP definition.

CREATE PROCEDURE dbo.MyProcedure
WITH EXECUTE AS 'domain\user'
AS
...

或者，您可以仅以用户身份执行某些命令SP:

EXECUTE AS USER = 'Domain\User'
  <Commands>
REVERT

在您的情况下，第二个选项可能更可取，以防止再次登录访问数据库.

The second option may be preferable in your situation to prevent giving another login access to the database.

这篇关于您可以将存储过程设置为始终以特定用户身份执行吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！