Grant User Role Execute Permission To All Stored Procedure Script

All SP Exec

GRANT EXECUTE TO roleRole01

GRANT EXECUTE TO usrUser01

All SP one SP at a time

Following script will try to add permissions (execute permissions) on all procedures on all databases on the server for the role defined.

declare @outcur as cursor
declare @qry varchar(2000), @name nvarchar(128), @ex varchar(2000)
declare @ick table (i int) 

Declare @Role nvarchar(128) = 'AppRole01'

set @outcur = Cursor Fast_Forward for
select name from sys.databases where name not in ('DB01','DB02')

open @outcur

Fetch Next from @outcur into @name
WHILE @@fetch_status >= 0  
begin  

	set @qry = 'use ' + QUOTENAME(@name) + ';'
	set @qry += 'Declare @c as Cursor, @q varchar(2000),@n nvarchar(128),@s nvarchar(128);'
	set @qry += 'set @c=cursor for select p.name,s.name from ' + QUOTENAME(@name) + '.sys.procedures p join ' + QUOTENAME(@name) + '.sys.schemas s on p.schema_id=s.schema_id;'
	set @qry += 'open @c; Fetch Next from @c into @n,@s; While @@fetch_status>=0 begin;'
	set @qry += 'set @q=''grant exec on ' + Quotename(@name) + '.'' + QuoteName(@s) + ''.'' + quotename(@n) + '' to ' + Quotename(@Role) + ''';'
	set @qry += 'Exec(@q);'
	set @qry += 'Fetch Next from @c into @n,@s; end;'

	set @ex = 'If exists (Select top 1 1 from ' + Quotename(@name) + '.sys.database_principals Where name = '''+ @Role + ''') select 1 else select 0'

	delete @ick

	insert @ick
	exec(@ex)
	
	If (select i from @ick) = 1
		Exec(@qry)
	else
		print 'no ' + @Role

	Fetch Next from @outcur into @name
end