CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
Geoff Dawdy
Geoff Dawdy

Reputation: 906

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.

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]

Upvotes: 1

Views: 7537

Answers (1)

JNK
JNK

Reputation: 65147

You want to use EXECUTE AS in the SP definition.

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

Alternatively, you can execute only certain commands as the user within the 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.

Upvotes: 5

Related Questions