Skip to main content

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.

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

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