2

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?

Improve this question
2
  • 1
    Grant the permissions in Model so that any newly created databases inheret them, but for existing databases you will have to create the perms in each (and the model db trick won't help for dbs restored from other servers)
    – Nic
    Jul 13, 2017 at 20:48
  • Great idea! Perhaps a daily job that checks to ensure the login has a user in every database and creates it if not will work in my pre-2014 environment.
    – SomeGuy
    Jul 14, 2017 at 13:21

2 Answers 2

Reset to default
5

I can't seem to find a way around this other than creating a user for the login for every single database

Starting with SQL Server 2014 the new CONNECT ANY DATABASE permission can be used to accomplish this

...and then granting permissions to the system tables explicitly.

You 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 permission

Also, there was no need to give VIEW ANY DATABASE TO permission because it's already granted to public, and VIEW 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 databases
  • VIEW DATABASE STATE for querying sys.dm_db_partition_stats
  • VIEW ANY DEFINITION for have the rights on any metadata
Improve this answer
1
  • That's great information. I didn't realize there was a new CONNECT ANY DATABASE permission. Unfortunately, in my scenario, most instances are PRE-2014. I think that leaves me with having to grant VIEW DATABASE STATE, VIEW ANY DEFINITION, and creating a user for the login on each database, including model.
    – SomeGuy
    Jul 14, 2017 at 13:20
0

Instead of creating the User in every database, you could create this user in the master database and GRANT VIEW ANY DEFINITION TO <USERNAME>. This will enable metadata visibility for the specified user in all databases.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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