Grant rights on all object types - Global statement 1

[AshleyRickards]

Does anyone know of a grant statement for granting rights to all objects.

grant select, update
on all tables
to user1

I have multiuser databases where I have hundredes of stored procedures and view etc.

TIA
Ashley Ashley L Rickards
SQL DBA

 

[rajeevnandanmishra]

Hi there,
If you are using SQL7.0 and higher, You can use the special procedure foreach to accomplish this task.
In sql6.5 or lower you can do like this
---------------------
Create procedure grantall as
create table #temporary (objectname varchar(50))
insert #temporary
select name from sysobjects where type in ('u','p')
order by name
declare @name varchar(50), @i int, @sql varchar(100)
select @name=''
select @i=count(*) from #temporary
while @i>0
begin
set rowcount 1
select @name=name from #temporary
where name > @name
select @sql='grant all on '+@name+' to public'
exec(@sql)
select @i=@i-1
end
set rowcount 0
drop table #temporary
return
---------------------


This procedure will give all rights for the tables and procedures of the current database. If you want to include other objects also just give their types also in the first select statement.

I hope this will move you in right direction.

 

[AshleyRickards]

Thanks. Where do I find this special procedure foreach? Ashley L Rickards
SQL DBA

 

[rajeevnandanmishra]

Hi there,
OK! that means you are using SQL 7.0 or above.
Sorry for not writing the full name of the procedure.
the actual procedure is sp_msforeachtable
you can perform your task by executing following code.
--------------
exec sp_MSforeachtable "GRANT ALL ON ? TO PUBLIC"
--------------

But keep in mind, this procedure can be used only for tables. So, if you want to run the job for other objects also, you have to write your own procedure.

 

[AshleyRickards]

Great! That will work for now and get me started.
Thanks again! Ashley L Rickards
SQL DBA