Solved

Cannot seem to use Execute As option in procedure

  • 22 February 2022
  • 2 replies
  • 63 views
MtHoen
Rank

Hello,

In our application I can let a user execute a Function with the Execute As option set to ‘sa’, but can't seem to do the same with a procedure. It gives the following error when executing:
 

Error message when executing

 

Subroutine options Settings in procedure


Seeing as I can execute a function as ‘sa’ just fine, user ‘sa’ must be available to me. Can anyone explain why this is happening?

For some context, I want my user to be able to use the results of xp_dirtree, but don't want to give him the rights of sysadmin. I need this to be a procedure since I can't use a temporary table in a function and I need to select rows from the output of xp_dirtree. 

Thanks in advance.

icon

Best answer by Anne Buit 23 February 2022, 08:53

View original
This topic has been closed for comments

2 replies

Anne Buit
Rank
Userlevel 7
Badge +5

‘sa’ is probably not a database user but a server login. When using execute as, you have to specify a database user. You probably will need to execute as ‘dbo’.

Note that this approach is a bit dangerous, security-wise. Retrieving the file names some other way would be recommended. To help with this, list folder as a process action is planned for 2022.2, a more elegant way to read the contents of a directory on disk.

MtHoen
Rank

DBO does work, you're right. Thanks. Glad to see the list folder process action will be available in june, I'll be sure to rebuild my solution then.

Terms & ConditionsCookie settings