There is a problem with permissions on sp_move_file.

If sp_move_file() is not directly invoked but called from within a procedure, the READ FILE and WRITE FILE privileges of the role owning the procedure are not sufficient. You have to explicitly grant execute permissions on sp_move_file (and an internal, undocumented function sp_real_copy_file) to the procedure owner.

This fix would be ok for me if there wasn't a big problem with it: The granted execute permissions are NOT written into an unload file! IOW, after an unload/reload of the db, they are gone.

We just run into the problem in a production environment and some automatic interfaces relying on that type of code were down for a day before we found out what's going on.

To SAP support: shall we open a case for it or is this sufficient for you?

Tested with 16.0.2127.

-- Beginning of sample code
create or replace role NewRole;

grant read file to NewRole;
grant write file to NewRole;

create or replace procedure NewRole.MoveFile(@fileName varchar(512))
sql security definer
begin
    declare sourcePath  varchar(1024);
    declare destPath    varchar(1024);
    declare result      int;

    set sourcePath = 'c:\temp\source\' + @fileName;
    set destPath = 'c:\temp\destination\' + @fileName;

    select dbo.sp_move_file(sourcePath, destPath) into result;
    message String('Move file result: ', result) type info to client;
end;

grant connect to NewUser;
grant execute on NewRole.MoveFile to NewUser;

-- The following call fails due to missing execute permissions on sp_move_file and then on sp_real_copy_file?
setuser NewUser;
call NewRole.MoveFile('test.txt');

-- Ok, fix it
setuser;
grant execute on sp_move_file to NewRole;
grant execute on sp_real_copy_file to NewRole;

-- Now it works
setuser NewUser;
call NewRole.MoveFile('test.txt');