Querying INFORMATION_SCHEMA.TABLE_STORAGE in BigQuery gives an access denied error

[pratzz]

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?

[Aris_O · Accepted solution]

Hi @pratzz 

Welcome back to Google Cloud Community.

Based on the error message it indicates that you do not have permission access with the TABLE_STORAGE view in your project which is part of INFORMATION_SCHEMA dataset.

The default settings for the permission to have access are the owner and project editors.

You may grant additional users and groups to access this data set by granting them roles via IAM.

You may use this reference to help you.
https://cloud.google.com/bigquery/docs/control-access-to-resources-iam?_ga=2.143013493.-1392753435.1...

https://cloud.google.com/iam/docs/granting-changing-revoking-access?_ga=2.143013493.-1392753435.1676...
 

[pratzz]

Thanks! I realized that being project owner doesn't automatically give you all other permissions. You still need to add one of those Bigquery permissions to the Principal. 

[Marcus31]

I have a similiar issue. When I query I get

"Access Denied: Table 408418:bigQuery_dataset.INFORMATION_SCHEMA.COLUMNS: User does not have permission to query table 408418:bigQuery_dataset.INFORMATION_SCHEMA.COLUMNS, or perhaps it does not exist in location US."

Under IAM I gave the the selected principal various permission rights such as Owner, BigQuery Admin and Container Analysis Occurrences Viewer, but still giving me the above error message. Any idea of why this might be the case?

[kolban]

Have a look at the following:

https://cloud.google.com/bigquery/docs/information-schema-columns

Let's make sure that your identity has the roles/permissions described at the article.  Let's also make sure that you are specifying the correct project/dataset.   Are you sure your project is called "408418"?  That doesn't "feel" like a good project name?