Querying INFORMATION_SCHEMA.TABLE_STORAGE in BigQuery gives an access denied error

Question

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?

Answer 1

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:

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