SQL access control error: Insufficient privileges to operate on schema 'INFORMATION_SCHEMA'

Question

When I am trying to execute the below query GRANT USAGE on INFORMATION_SCHEMA, I am getting below error

GRANT MONITOR ON SCHEMA DEMO_DB.INFORMATION_SCHEMA TO ROLE ALATIONTEST; GRANT USAGE ON SCHEMA DEMO_DB.INFORMATION_SCHEMA TO ROLE ALATIONTEST;

I have tried with different roles SYSADMIN, ACCOUNTADMIN, SECURITYADMIN still no luck.

Also, I could run the above GRANT query for other schemas, Only getting error with INFORMATION_SCHEMA

Please help on this.

Does the answer here help? stackoverflow.com/questions/58294920/… — Suzy Lockwood
– Suzy Lockwood, Commented May 15, 2020 at 16:36
What problem are you having that requires you to grant access to the information_schema? Is the alation role not able to see the information_schema for a database? — Simon D
– Simon D, Commented May 15, 2020 at 23:56
I need to fetch the Metadata of all the schema, tables, columns from the respective DB from INFORMATION_SCHEMA. So for that, Do I need to provide the privilege to my service account? — Vathinth T
– Vathinth T, Commented May 18, 2020 at 19:11

Mike Walton · Accepted Answer · 2020-05-15 16:51:26Z

0

The INFORMATION_SCHEMA schema is read-only for all users. No explicit grants to that schema are necessary.

answered May 15, 2020 at 16:51

Mike Walton

7,4592 gold badges14 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

What about the tables and views under the INFORMATION_SCHEMA? Do we need to provide select privilege for those objects?

The objects that are available are mostly views or table functions, and they will all be read-only by default. No need to grant anything to roles.