Grant select permission on all Tables to a DB role

Hello,
I got Win2k Advanced Server with SQL 2K running.
I am trying to figure out one single query will give a permission to all
tables under a database. A database has 20 - 30 tables with several custom
roles and it is pain to run a query that give a permission on one table at a
time.
Grant select on my_table to my_role
Grant update on my_table1 to my_role1
Can any one turn a light for me on this'
Thanks in advance.
SangHunThere might be another way, but you could use the sp_msforeachtable sproc.
Please note that it's undocumented/unsupported, so insert usual warnings
here (don't rely on it in production code or expect it to be there in the
next release, etc, etc.)
But I digress...
sp_msforeachtable 'grant select on ? to my_role'
Will grant select on every table in the current database to my_role.
Here is an article with more info on that sproc and another related sproc,
sp_msforeachdb:
http://www.dbazine.com/larsen5.shtml
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> Hello,
> I got Win2k Advanced Server with SQL 2K running.
> I am trying to figure out one single query will give a permission to all
> tables under a database. A database has 20 - 30 tables with several
custom
> roles and it is pain to run a query that give a permission on one table at
a
> time.
> Grant select on my_table to my_role
> Grant update on my_table1 to my_role1
> Can any one turn a light for me on this'
> Thanks in advance.
> SangHun|||Why can't you just add the role to db_datareader and db_datawriter?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eZvZS68eEHA.3100@.TK2MSFTNGP10.phx.gbl...
There might be another way, but you could use the sp_msforeachtable sproc.
Please note that it's undocumented/unsupported, so insert usual warnings
here (don't rely on it in production code or expect it to be there in the
next release, etc, etc.)
But I digress...
sp_msforeachtable 'grant select on ? to my_role'
Will grant select on every table in the current database to my_role.
Here is an article with more info on that sproc and another related sproc,
sp_msforeachdb:
http://www.dbazine.com/larsen5.shtml
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> Hello,
> I got Win2k Advanced Server with SQL 2K running.
> I am trying to figure out one single query will give a permission to all
> tables under a database. A database has 20 - 30 tables with several
custom
> roles and it is pain to run a query that give a permission on one table at
a
> time.
> Grant select on my_table to my_role
> Grant update on my_table1 to my_role1
> Can any one turn a light for me on this'
> Thanks in advance.
> SangHun|||Does db_reader role has select permission on all existing tables?
I have seem that even I added a user to the db_reader role, the user didn't
have select permission to any table until I grant permission to specific
table for the user.
I thought I might create a read-only role and manage from there.
Thanks,
"Tom Moreau" wrote:

> Why can't you just add the role to db_datareader and db_datawriter?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eZvZS68eEHA.3100@.TK2MSFTNGP10.phx.gbl...
> There might be another way, but you could use the sp_msforeachtable sproc.
> Please note that it's undocumented/unsupported, so insert usual warnings
> here (don't rely on it in production code or expect it to be there in the
> next release, etc, etc.)
> But I digress...
> sp_msforeachtable 'grant select on ? to my_role'
> Will grant select on every table in the current database to my_role.
> Here is an article with more info on that sproc and another related sproc,
> sp_msforeachdb:
> http://www.dbazine.com/larsen5.shtml
>
> "SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
> news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> custom
> a
>
>|||Thanks Addm,
I will try that out on testing env.
SangHun
"Adam Machanic" wrote:

> There might be another way, but you could use the sp_msforeachtable sproc.
> Please note that it's undocumented/unsupported, so insert usual warnings
> here (don't rely on it in production code or expect it to be there in the
> next release, etc, etc.)
> But I digress...
> sp_msforeachtable 'grant select on ? to my_role'
> Will grant select on every table in the current database to my_role.
> Here is an article with more info on that sproc and another related sproc,
> sp_msforeachdb:
> http://www.dbazine.com/larsen5.shtml
>
> "SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
> news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> custom
> a
>
>|||The db_reader role has SELECT permission on all tables and views. I've not
seen the problem you describe. Perhaps you can give us a repro script?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:3BF1E1D9-412B-4282-B04F-DCF952713E36@.microsoft.com...
Does db_reader role has select permission on all existing tables?
I have seem that even I added a user to the db_reader role, the user didn't
have select permission to any table until I grant permission to specific
table for the user.
I thought I might create a read-only role and manage from there.
Thanks,
"Tom Moreau" wrote:

> Why can't you just add the role to db_datareader and db_datawriter?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eZvZS68eEHA.3100@.TK2MSFTNGP10.phx.gbl...
> There might be another way, but you could use the sp_msforeachtable sproc.
> Please note that it's undocumented/unsupported, so insert usual warnings
> here (don't rely on it in production code or expect it to be there in the
> next release, etc, etc.)
> But I digress...
> sp_msforeachtable 'grant select on ? to my_role'
> Will grant select on every table in the current database to my_role.
> Here is an article with more info on that sproc and another related sproc,
> sp_msforeachdb:
> http://www.dbazine.com/larsen5.shtml
>
> "SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
> news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> custom
at[vbcol=seagreen]
> a
>
>