/*
Below code will grant execute permission to all SP's in the DB.
Using below script we can grant permission to all other objects/other permission
in the DB to diffrent users.
*/
select name into #temp_sp_list from sys.objects where type ='P' order by 1
declare @sp_count int,@sp_name varchar(50)
--select top 1 @sp_name=name from #temp_sp_list
--print @sp_name
select @sp_count=count(*) from #temp_sp_list
while(@sp_count>0)
begin
select top 1 @sp_name=name from #temp_sp_list
EXEC ('grant execute on [' + @sp_name + '] to SBUReportStaging') -- Change the user name/permission;
delete from #temp_sp_list where name=@sp_name
select @sp_count=count(*) from #temp_sp_list
print @sp_count;
end
drop table #temp_sp_list
-- Thanks to Rakesh Rao :)
Below code will grant execute permission to all SP's in the DB.
Using below script we can grant permission to all other objects/other permission
in the DB to diffrent users.
*/
select name into #temp_sp_list from sys.objects where type ='P' order by 1
declare @sp_count int,@sp_name varchar(50)
--select top 1 @sp_name=name from #temp_sp_list
--print @sp_name
select @sp_count=count(*) from #temp_sp_list
while(@sp_count>0)
begin
select top 1 @sp_name=name from #temp_sp_list
EXEC ('grant execute on [' + @sp_name + '] to SBUReportStaging') -- Change the user name/permission;
delete from #temp_sp_list where name=@sp_name
select @sp_count=count(*) from #temp_sp_list
print @sp_count;
end
drop table #temp_sp_list
-- Thanks to Rakesh Rao :)
No comments:
Post a Comment