I have a set of SQL scripts to manage definitions of stored procedures in my database. One of my goals is to make these scripts safe for repeated execution. If the SP already exists, update it, otherwise create it. I need to preserve whatever GRANTs have been applied by the DBA. Usually it is easy enough to:

create or alter procedure sch.activity as
  @parm int
begin
  select 'yada yada...';
end;

GRANTs survive. This strategy fails when the SP uses a possibly changed table-valued parameter. CREATE OR ALTER isn’t available. I need to drop and create procedure. When I do that the GRANTs are lost.

I might preserve the grants in a variable, then drop and create the SP and type, then re-apply the GRANTs. But, since I have to drop and create the SP, there are ‘go’s separating the actions, and variable scope is lost.

I could preserve the permissions in a temporary table. I can’t use a single # because those are lost between ‘go’s so I have to use ##tables, and be careful to manage the lifetime of the temporary, in case the DBA needs to run this 24 times in a row in a few seconds, or something.

Now I have a more complicated script

declare @grant nvarchar(max);
select @grant = 'insert gnarly query of sys.procedures and sys.premissions with object_id()s and stuff here'
insert @grants g into ##grants;
drop procedure if exists ...
go
drop type if exists ...
go
create type tvp as ...
go
create or alter procedure sch.activity as
  @parm tvp
begin
  select 'yada yada...';
end;
go
if object_id('tempdb..##grants') is not null
begin
    declare @g nvarchar(max);
    select @g=g from ##grants;
    if @g is not null
    begin
        exec @g;
    end
    drop table ##grants;
end

There is more I could do to bullet-proof the script. This seems a lot of work, and a lot of chance for mistakes. The new code has very little to do with the intent of the SP. Is there a better way?