Bulk GRANT statements

On occassion I have to set close to 100 seperate
permissions on various objects, and obviously that gets
tedious with enterprise manager.
In my Oracle days I would 1) create a SELECT statement
that prints out the GRANT statements 2) output the SELECT
to a file, and 3) execute the file in the Oracle
equivalent of Sql Query Analyzer.
I know how to create the GRANT statements (1), but I dont
know how to perform 2 and 3 (or whatever is the better
method) in SQL Server 2000.
Any help would be greatly appreciated.
MarkOnce you have granted the permissions using Enterprise
Manager, you can output them to a file using the script
utility in the tools menu. You can then open the script,
edit it, and execute it in Query Analyzer.
Bill
>--Original Message--
>On occassion I have to set close to 100 seperate
>permissions on various objects, and obviously that gets
>tedious with enterprise manager.
>In my Oracle days I would 1) create a SELECT statement
>that prints out the GRANT statements 2) output the
SELECT
>to a file, and 3) execute the file in the Oracle
>equivalent of Sql Query Analyzer.
>I know how to create the GRANT statements (1), but I
dont
>know how to perform 2 and 3 (or whatever is the better
>method) in SQL Server 2000.
>Any help would be greatly appreciated.
>Mark
>.
>

Bulk Grant Select failing

Hi there
I'm still finding my way in SQL server so the problem might be very simple (hopefully...).
Would anybody have any idea why:

grant select on table1 to ReadGroup

works fine, and

grant create table to ReadGroup

works fine, yet

grant select to ReadGroup

results in
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'to'.?

Any help would be immeasurably appreciated
Cheers!Hi
I think you need to specify what table they are being granted select ON...
ie.
GRANT SELECT
ON table
TO user
GO

add them to db_datareader role if you want database wide select ...
des|||if u are looking for granting select permissions to all the user tables
then :

declare @.table varchar(100)
select [name] into #temp from sysobjects where xtype='u'
while exists (select * from #temp)
begin
select top 1 @.table=[name] from #temp
exec ('Grant select on '+@.table+' to ReadGroup')
delete from #temp where [name]=@.Table

end
drop table #temp|||Hi

Thanks for this - Yep, wanted to set permissions to over 300 tbales in one go. Wondered if the failure was due to not specifying object types but wasn't sure how to - thanks for your help guys

Cheers