0

Microsoft documentation says about db_datareader role that:

db_datareader: Members of the db_datareader fixed database role can read all data from all user tables and views. User objects can exist in any schema except sys and INFORMATION_SCHEMA.

The last sentence is confusing for me. I think it means that users whose only role is db_datareader cannot query things inside INFORMATION_SCHEMA, but I made a user with only that role for a database and I successfully ran queries like below on that database:

SELECT * FROM INFORMATION_SCHEMA.TABLES

Doesn't this contradict that last sentence? What is the correct interpretation of the last sentence?

Improve this question

1 Answer 1

Reset to default
2

What that sentence is saying is that you cannot create your own table in the INFORMATION_SCHEMA schema. I.e., below is not allowed:

CREATE TABLE INFORMATION_SCHEMA.mytable(c1 int)

As for what meta-data that is exposed through these views, we have the same rules as when querying catalog views: you see the objects that you have permissions to use (to phrase it short). Since db_datareader has permissions to select from all tables and views, they see the existence of all tables and views (except where they have deny}.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.