Cross-site queries on SEDE fail with “The EXECUTE permission was denied”

Question

I just noticed that any cross-site queries using dynamically generated SQL on the Stack Exchange Data Explorer (SEDE) now fail with the error message:

The EXECUTE permission was denied on the object 'sp_executesql', database 'mssqlsystemresource', schema 'sys'.

Typically this message is repeated many times as the code tries to loop over all SE sites:

I don't know exactly when this stopped working, but it must've been sometime within the last two months, as it was still working fine in mid-August.

I'm not sure if this technically constitutes a bug, since AFAIK this feature was never explicitly supported. However, the change did break a number of useful queries, such as this one for making a backup of all your posts on the SE network and this one that lists duplicates of deleted questions and uses the cross-site query feature to exclude migrated questions. (I've edited the latter query to temporarily disable the cross-site part, so that it at least mostly works.)

So my questions are:

1. Can this feature please be re-enabled (e.g.) by adding the necessary permissions?
2. If not, is there any work-around?

---

Ps. I just found this related bug report from early September which looks similar, but shows a different error message and is marked as status-completed. Apparently this isn't the first time this feature broke recently.

Answer 1

This should be resolved now. Recently we restricted access to many of the system objects inside of SEDE and the change impacted the ability to execute that stored procedure.

I updated the permissions this morning and you should be able to execute sp_executesql again.

Thanks for reporting it.

Side Note: Another way to execute dynamic SQL is to use exec(@querystring) or execute(@querystring) and then you're not executing a stored procedure.