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?
Best Answer
Starting with SQL Server 2014 the new
CONNECT ANY DATABASE
permission can be used to accomplish thisYou have no need to grant permissions on system tables, they are visible to any user. But the visibility of objects they contain depends on the permissions the user has: he can see only those objects on which it has permissins: Metadata Visibility Configuration
And this can be solved by giving to your login
VIEW ANY DEFINITION
permissionAlso, there was no need to give
VIEW ANY DATABASE TO
permission because it's already granted to public, andVIEW SERVER STATE TO
is not required for the metadata you described retrieving.So, all you need are these permissions:
CONNECT ANY DATABASE
to access all the databasesVIEW DATABASE STATE
for queryingsys.dm_db_partition_stats
VIEW ANY DEFINITION
for have the rights on any metadata