SQL Object Access Question

[SQLDBA201400]

I need to grant select access to an account on these:

sys.schemas

sys.foreign_keys

INFORMATION_SCHEMA.VIEWS

sp_tables

sp_statistics

1) Can this be granted only for a specific database on the Instance or is it at server level?

2) Does grant View Definition access grant access to all of the above?

Thanks!

[#2]

@SQLDBA201400 

Hi.

1. Excluding sp_statistics, the remainder can be granted at the database level. That said, sp_statistics can only return information for underlying objects that the user has access to, which of course are objects in the database, meaning database grants are still the control plane;
2. No, it does not.

Cheers,

Lain

[#3]

@SQLDBA201400 , by default every user can query those system views, but they return only informations for objects the user has access to.

See for example sys.schemas (Transact-SQL) - SQL Server | Microsoft Learn

=> Permissions => "Public role"