6

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?

  • What exactly are you trying to accomplish? What problem / scenario are you facing that you feel will be solved by having this? Are you wanting to allow for ad hoc access, or will operations all be done though stored procedures? Just curious as there might be another means of accomplishing that goal, depending on what it is. – Solomon Rutzky May 17 '17 at 13:34
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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