Granting Permissions on Multiple Tables

I created a new Role called GRPSELECT. I want to give this group the abilit
y to only run the SELECT statement on all the tables in my database. Right
now I can run "GRANT SELECT ON table TO GRPSELECT" in Query Analyzer, but it
only allows me to do this
to one table at a time.
How can I accomplish this to all 600 tables in the database without manually
typing in every single table?
Thanks in advance!Instead of creating a new role you can add these users/group to
db_datareader fixed db role.
Members of db_datareader fixed db role have select permissions on any
objects in the db.
Thanks,
Lyudmila Fokina
Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only
Disclaimer: This posting is provided "AS IS" with no warranties, and confers
no rights.
"Jon Jones" <Jon Jones@.discussions.microsoft.com> wrote in message
news:96F7208A-EA64-467A-8AD2-5477782ED32E@.microsoft.com...
> I created a new Role called GRPSELECT. I want to give this group the
ability to only run the SELECT statement on all the tables in my database.
Right now I can run "GRANT SELECT ON table TO GRPSELECT" in Query Analyzer,
but it only allows me to do this to one table at a time.
> How can I accomplish this to all 600 tables in the database without
manually typing in every single table?
> Thanks in advance!|||Jon,
You can use Transact-SQL to generate the script for you:
Ex:
SELECT 'GRANT SELECT ON ' + so.name + ' TO GRPSELECT'
FROM dbo.sysobjects so
WHERE so.type = 'u'
This output can then be copied.
Randy Dyess
"Jon Jones" wrote:

> I created a new Role called GRPSELECT. I want to give this group the ability to o
nly run the SELECT statement on all the tables in my database. Right now I can run
"GRANT SELECT ON table TO GRPSELECT" in Query Analyzer, but it only allows me to do
thi
s to one table at a time.
> How can I accomplish this to all 600 tables in the database without manual
ly typing in every single table?
> Thanks in advance!sql