17

For example, a

SELECT * FROM sysdatabases;

results

enter image description here

maybe some synchronization is running and it is only a temporary situation?

Note, no network-wide query will work, if you make this permanent.

Improve this question
8
  • Do you have an example query besides this one that isn’t working?
    – Taryn StaffMod
    Commented Sep 6, 2019 at 23:21
  • 2
    None of the network-wide queries work. Because all the sites have a different DB, to query the whole network, we need to loop them in t-sql. To do that, we need to get the list of the databases. This is in the sysdatabases table in t-sql, we don't have access to this table now.
    – peterh
    Commented Sep 6, 2019 at 23:30
  • 1
    @Taryn Earlier I've experienced that if a database synchronization is going on, weekly for some hours, some thing starts to behave mysteriously. For example, some temporary databases appeared and then disappeared and so. But we always had access to the sysdatabases. Now we don't.
    – peterh
    Commented Sep 6, 2019 at 23:32
  • 1
    Confirmed. Can we at least get a master site table if this change is permanent?
    – Awesome Poodles
    Commented Sep 6, 2019 at 23:35
  • @Taryn Queries are saved into the SEDE DB (thus, become linkable) only on successful execution. Thus, I can't link not working queries (but I can show the link of the once worked queries, and I can also copy not working queries here, the second is what I did).
    – peterh
    Commented Sep 6, 2019 at 23:37
  • @AwesomePoodles For example, the Sites.xml in the archive.org backups would be very useful. If we would have that table on the SEDE.... how wonderful would it be..........
    – peterh
    Commented Sep 6, 2019 at 23:39
  • @peterh The link above is fine, I just needed to see an example. I'm investigating it.
    – Taryn StaffMod
    Commented Sep 6, 2019 at 23:40
  • 2
    @Taryn The archive.org dumps have a Sites.xml file. This is coming nearly surely from a real table from the productive database. Could you please say to the "higher levels", that this Sites table is what we really need? It would be far more useful, than a restored access to the sysdatabases!
    – peterh
    Commented Sep 6, 2019 at 23:41

1 Answer 1

Reset to default
18

This has been fixed and the network-wide queries should work again.

Earlier today, we did some permissions updates to the account used for SEDE and inadvertently broke the queries by locking down the sys schema.

We went ahead added SELECT permissions back to a handful of system items including:

  • sys.all_columns
  • sys.all_objects
  • sys.all_views
  • sys.columns
  • sys.databases
  • sys.objects
  • sys.indexes
  • sys.stats
  • sysdatabases
  • sys.tables
  • sys.types
  • sys.views

It definitely wasn't our intention to break the queries when we made the change and rolled back some of the permissions as a result. Thanks for reporting it.

Improve this answer

You must log in to answer this question.

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