How to GRANT object permission to a user defined server role?

Question

I want to create logins that are granted permission to access

• all user databases

• view all definitions

• select all records of all tables

To me it seems to be too much overhead to assign the login to each database and add it to a custom db role or to a default db role like db_datareader in each db.

Instead I tried to use a user defined server role.

• But how can I assign the required permissions to a user-defined server role to allow view any definition and db_datareader for all dbs?

• Is this even possible?

Answer 1

To access all databases you should give the CONNECT ANY DATABASE permission.

To view all the definitions you should grant the VIEW ANY DEFINITION permission.

To select all data from all user tables you can grant SELECT ALL USER SECURABLES starting with SQL Server 2014, but it will permit you to select data only from all accessible databases, so without CONNECT ANY DATABASE it gives you nothing.

So, even if you have SQL Server 2014 and higher, you should give all three permissions to accomplish what you want:

• CONNECT ANY DATABASE
• VIEW ANY DEFINITION
• SELECT ALL USER SECURABLES

There is no need to grant VIEW ANY DATABASE as it's already granted to public.