--:SetVar Procedure "AUX:GrantExecOnProcedures"
if exists( select * from [sysobjects] (nolock) where [type]='P' and [name]=N'AUX:GrantExecOnProcedures' )
   drop procedure [AUX:GrantExecOnProcedures]
GO
/* Translated by $SQLTRANS utility (SQL-file technology). Method author: Sergei Y.Kitáev.
** Script translation date-time: 2022-11-27 00:29:29.24 (stamp of SQL-file generation). */
create procedure [AUX:GrantExecOnProcedures]
   @ProcedureNamePattern   nvarchar(max),
   @UserOrGroupName        nvarchar(max)
AS
/*==================================================================================================
# SQLAUX scripting library | Author: Sergei Y.Kitáev
----------------------------------------------------------------------------------------------------
#
# EXECUTION RIGHT ASSIGNMENT — ON SET OF STORED PROCEDURES (BY NAME PATTERN, ON EXISTING OBJECTS):
#
# === Usage: ===
# exec [AUX:GrantExecOnProcedures] '<Prefix token>:%', <User name or user group name>
# exec [AUX:GrantExecOnProcedures] '<Prefix token>\_%', <User name or user group name>
#
# ( Character “\” serves as “escape” for indication,
#   that “_” symbol is not used as placeholder. )
#
# *** CAUTION: ***
# USE THIS PROCEDURE WITH HIGH CAUTION
# TO AVOID UNINTENDED EXECUTION RIGHT ASSIGNMENT!
#
==================================================================================================*/
begin begin try set xact_abort off; set ansi_nulls,ansi_null_dflt_on,quoted_identifier,arithabort,nocount on; set implicit_transactions off; declare @initial_tran_count_aux int, @is_local_tran_open_aux bit; select @initial_tran_count_aux=@@trancount, @is_local_tran_open_aux=0; end try begin catch throw; end catch; begin try
----------------------------------------------------------------------------------------------------

if @ProcedureNamePattern is null or @UserOrGroupName is null
   exec [AUX:RaiseIncorrectProcedureCall] 'AUX:GrantExecOnProcedures'

if CHARINDEX(' ',@ProcedureNamePattern)>0 or
   @ProcedureNamePattern='%' or SUBSTRING(@ProcedureNamePattern,1,2)='%%'
   exec [AUX:RaiseError] 'AUX:GrantExecOnProcedures',
      'Incorrect pattern for procedure name.',
      N'Некорректный шаблон имени процедуры.'

----------------------------------------------------------------------------------------------------

declare @PROCEDURE_LIST table ([Name] sysname primary key)
insert @PROCEDURE_LIST
   select [name] from [sysobjects] (nolock)
   where [type]='P' and [name] like @ProcedureNamePattern escape '\'

while 0=0
begin
   declare @ProcedureName sysname
   select @ProcedureName=MIN([Name]) from @PROCEDURE_LIST
   if @ProcedureName is null break

   exec( 'grant execute on ['+@ProcedureName+'] to ['+@UserOrGroupName+']' )

   delete @PROCEDURE_LIST where [Name] = @ProcedureName
end

----------------------------------------------------------------------------------------------------
if @is_local_tran_open_aux=1 raiserror ('Not closed local transaction was detected, previously opened by BEGIN_TRANSACTION_AUX macro.',11,1); end try begin catch if @is_local_tran_open_aux=1 and (@@trancount>@initial_tran_count_aux or xact_state()=-1) rollback transaction; set @is_local_tran_open_aux=0; throw; end catch; end
/*================================================================================================*/
GO
