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
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?