1

In SQL Azure my understanding of db_datareader is that it is allowed to SELECT all user tables.

I couldn't find a definition of exactly what a user table is, but I assumed it was all of the non-builtin tables and views, and excluded all the views and tables that are in the sys schema.

Why then can members run the following statements?

SELECT TOP (5) * FROM [sys].tables

SELECT TOP (5) * FROM [sys].[all_views];

SELECT * FROM sys.dm_exec_sessions;

SELECT TOP (5) * FROM [sys].objects

... and more

4

Metadata visibility configuration doesn't work in the same was as permissions for User tables - the rights are included implicitly whenever rights to underlying objects are granted.

Take the following example from the docs:

SELECT name, object_id  
FROM sys.tables  
WHERE name = 'myTable';  
GO  

The above returns no results for users that have been granted no rights on those underlying tables. It therefore follows that if you grant db_datareader (IE SELECT on all tables / views), you're also implicitly granting access to sys.tables for all Tables / Views in that database.

This is true for all GRANT states (IE DENY won't cause the user to see metadata for related objects, but GRANT will)

In summary, granting access to a user object also grants access to the metadata for that object, exposed via the system catalog.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.