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?

I hope that was all clear.

Thanks in Advance, Curtis

  • 2
    I'm not a dba but I think you can just add that role to the db_datareader role – Crowcoder Oct 12 '15 at 13:37

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
| improve this answer | |
  • I have used the above in our Test environment and having a user test it now. If it works, then I will select this as my answer...If not, then I will be back for more help. Thanks!! Stay Tuned!! – Curtis Beiersdorf Oct 12 '15 at 14:31
  • Worked perfectly. Thank you very much for the answer and quick reply...I cannot upvote this yet do to lack of rep... – Curtis Beiersdorf Oct 12 '15 at 15:11
  • Great worked for me. – Viraj Wadate Nov 26 '19 at 12:02

You can generate and run a sql with sysobjects system table as follows:

select 'GRANT EXEC ON dbo.' + name + ' TO myspecialrole GO' from mydb..sysobjects where type = 'U'
| improve this answer | |

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.