Skip to main content
Solved

Cannot seem to use Execute As option in procedure

  • February 22, 2022
  • 2 replies
  • 63 views
MtHoen
Apprentice

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.

Best answer by Anne Buit

‘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.

View original
Did this topic help you find an answer to your question?
This topic has been closed for replies.

2 replies

Anne Buit
Community Manager
Forum|alt.badge.img+5
  • Anne Buit
  • Community Manager
  • 653 replies
  • Answer
  • February 23, 2022

‘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
1 person likes this
  • MtHoen
    MtHoen
MtHoen
Apprentice
  • MtHoenApprentice
  • Author
  • 7 replies
  • February 23, 2022

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.

Anne Buit
1 person likes this
  • Anne Buit
    Anne Buit
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings