How to best write a TSQL script to create or alter a stored procedure that has a table-value type as parameter, preserving GRANTs

Question

I have a set of SQL scripts to manage definitions of stored procedures in my database. (This will be hosted on a SQL Azure 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. The implementation of the SP is independent of decisions about who can execute it. Grants change depending on where the data model is posted, so grants can't be part of the create script. 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 to me as a strategy, as in this case it generates an error. 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?

Answer 1

You can keep the permissions by throwing in an extra CREATE/ALTER with an empty body.

CREATE OR ALTER PROCEDURE dbo.proc1 as ; -- removes dependency on tvp type
GO

DROP TYPE tvp ...
GO

CREATE TYPE tvp ...
GO

ALTER PROCEDURE dbo.proc1 
(
@p1 tvp READONLY
) AS
BEGIN 
...
END
GO