Granting Select access to all tables within a specific Database in MS SQL

I have a server that has multiple databases that are contained within that same server/project. I am using MS SQL Server 2012.

I have a special role set up with 3 users that are apart of that role. I would like to grant Select access to the role for all tables that are within 1 specific database.

The database in question is an archive database that has archive tables for each month for the past 12 years. When originally creating the role, I granted access by just running the following:

GRANT SELECT ON [dbo].[myarchivetable] TO myspecialrole

go

This worked great because I would just have to change the year and month and run it all as 1 script.

I am now finding out that they are needing Select access to every table in the archive database. I could do the above method, however Having to write all of that out 132 times, it would be very easy to make a mistake.

How can I easily add the myspecialrole to every table within that database?

To grant SELECT permissions on the whole database use this:
USE
GRANT SELECT ON DATABASE :: TO


where

• ` is user






• is database name




Granting permissions on schema doesn't help, simple reason: if new schemas occur the user will not have permissions.

Granting permissions on all objects doesn't for the same reason, the user will not have permissions on new objects created after GRANT` event.

StackExchange Database Administrators Q#117759, answer score: 5