:SetVar Procedure "AUX:RevokeExecOnProcedures"
:r $(DropProcedureScript_AUX)
create procedure [$(Procedure)]
   @ProcedureNamePattern   $(dt_String_AUX),
   @UserOrGroupName        $(dt_String_AUX)
AS
/*==================================================================================================
# SQLAUX scripting library | Author: Sergei Y.Kitáev
----------------------------------------------------------------------------------------------------
#
# EXECUTION RIGHT REVOCATION — ON SET OF STORED PROCEDURES (BY NAME PATTERN, ON EXISTING OBJECTS):
#
# === Usage: ===
# exec [AUX:RevokeExecOnProcedures] '<Prefix token>:%', <User name or user group name>
# exec [AUX:RevokeExecOnProcedures] '<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 REVOCATION!
#
==================================================================================================*/
$(BEGIN_AUX)
----------------------------------------------------------------------------------------------------

if @ProcedureNamePattern is null or @UserOrGroupName is null
   exec [AUX:RaiseIncorrectProcedureCall] '$(Procedure)'

if CHARINDEX(' ',@ProcedureNamePattern)>0 or
   @ProcedureNamePattern='%' or SUBSTRING(@ProcedureNamePattern,1,2)='%%'
   exec [AUX:RaiseError] '$(Procedure)',
      '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 $(BACKSLASH_AUX)

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

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

   delete @PROCEDURE_LIST where [Name] = @ProcedureName
end

----------------------------------------------------------------------------------------------------
$(END_AUX)
/*================================================================================================*/
GO
