11

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:

Screenshot

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 . Apparently this isn't the first time this feature broke recently.

|improve this question|||||
  • 5
    I'm pretty sure I know what caused this and when. Let me see what I can do to unstick this. – Taryn♦ Oct 10 '19 at 15:14
  • 3
    I kinda hope the answer involves an actual stick ... – Bart Oct 10 '19 at 15:56
  • 4
    @bart I will never give away my secrets – Taryn♦ Oct 10 '19 at 16:26
8

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.

|improve this answer|||||
  • 1
    Thanks! I can confirm that my cross-site queries are working again. Also, special thanks for going the extra mile and providing the alternate solution. Unfortunately my T-SQL skills are rather weak, as I use it only on SEDE, so a lot of my "fancier" code tends to be of the copy-paste or "monkey see, monkey do" variety. It's always nice to learn new and (possibly) better ways of doing things. – Ilmari Karonen Oct 11 '19 at 17:47
  • 2
    Ps. Now that I can actually test it, here's a post describing my latest cross-site monstrosity that finds broken links to questions and answers between different SE sites. Maybe someone will find it useful. :) – Ilmari Karonen Oct 11 '19 at 22:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .