RE: Granting Permissions on Multiple Tables

From: Randy (Randy_at_discussions.microsoft.com)
Date: 06/24/04 

Date: Thu, 24 Jun 2004 09:04:36 -0700

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