1

I'm trying to query the TABLE_STORAGE view in my BigQuery project to get storage information about my tables. I'm using the following query -

SELECT * FROM region-us.INFORMATION_SCHEMA.TABLE_STORAGE

but I get the following error -

Access Denied: Table testing-67734:region-us.INFORMATION_SCHEMA.TABLE_STORAGE: User does not have permission to query table testing-67734:region-us.INFORMATION_SCHEMA.TABLE_STORAGE, or perhaps it does not exist in location US.

I've used this query in my existing project from years ago as well as a freshly created project for testing, but I'm getting the same error in both cases. The error is the same even if I query TABLES or TABLE_STORAGE_BY_PROJECT instead of TABLE_STORAGE. In all cases, I'm the project owner. What am I doing wrong?

Pratzz
  • 107
  • 1
  • 7

1 Answers1

2

I was able to execute it with your exact query that you have posted.

SELECT * FROM region-us.INFORMATION_SCHEMA.TABLE_STORAGE

I used us-central1 but just us works too, but it will require my project a huge resource to query so i just settled for us-central1

Output:

enter image description here

It is possible that this is a permission issue. I assume you have owner role here, but just to be safe can you try adding these roles too in your user?

  • roles/bigquery.admin
  • roles/bigquery.dataEditor
  • roles/bigquery.metadataViewer
  • roles/bigquery.dataViewer

Reference: https://cloud.google.com/bigquery/docs/information-schema-table-storage

If these still fails, I would suggest to create a case or contact google support for this, as they can assess if there is a user or any org/project policy conflict: https://cloud.google.com/contact

Nestor Ceniza Jr
  • 976
  • 3
  • 11
  • 1
    bruh, this is insane! It worked! I thought being project owner would mean you would have all other permissions, but apparently, that's not so. – Pratzz Apr 07 '23 at 19:49
  • 1
    Wow! This is insane! By definition, `Owner` should have all the necessary permissions. Adding myself to the role `BigQuery Admin` helped in my case. – Vishwas M.R Apr 24 '23 at 12:37
  • I keep getting an access denied error, I'm in europe and also tried replacing the region to europe-west2 – Topde Jul 26 '23 at 20:29