Our development team has created some SSRS reports that retrieve metadata only (no 'real' data) from every database. I am trying to lock down the service account running these reports to the minimum privileges required.
The queries only select from the system tables in each database such as:
sys.tables
sys.extended_properties
SYS.objects
SYS.schemas
sys.dm_db_partition_stats
sys.indexes
I thought the following would do it, but no such luck.
GRANT VIEW ANY DATABASE TO [DOMAIN\LOGIN]
GRANT VIEW SERVER STATE TO [DOMAIN\LOGIN]
GRANT VIEW DATABASE STATE TO [DOMAIN\LOGIN]
Querying system tables gives the error:
The server principal "DOMAIN\LOGIN" is not able to access the database "DATABASENAME" under the current security context.
I can't seem to find a way around this other than creating a user for the login for every single database and then granting permissions to the system tables explicitly. This presents a new set of challenges for all future databases. Am I missing something or is there no simple way to grant this access across the board to all databases without granting the "sysadmin" server-level role?