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?
If all your tables are in the dbo schema then this should work, you can just deny single tables if required

GRANT SELECT ON SCHEMA :: [dbo] TO myspecialrole