Apologies for the delayed response. Not wanting to resort to CDOC, we found a workaround as follows:
- We split the stored procedure into two, the first with hard-coded delete statements against a different database (not requiring any additional security), the second with dynamic statements against the main database using EXEC(@SQL); Create both procedures on the main database.
- Create a user without login on the main database and add to the db_datareader and db_datawriter database roles.
- Run the second stored procedure in the context of this new user (i.e. EXECUTE AS 'MyUser').
This seems to have been the workaround for us, but I am sure CDOC could have been of help too so I will mark both as part of the answer, thank you for your help.