Stored Proc for SQL Server and DB2 UDB

We often grant permissions against table for users. Some use the GUI, some just script it out. I wrote a quick stored procedure that will take couple of input parameter and run the grant against the tables.  Just for the fun of it, I wrote it for both DB2 UDB and SQL Server.  I know there are more than one way to write, but here is one way.

DB2 UDB:

CREATE PROCEDURE Grant_TablePermissions (
                            IN  oschema  varchar(20),
                            IN  permissions varchar(30),
                            IN  grantee varchar(30),
                            OUT  counter  smallint
                            )
    DYNAMIC RESULT SETS 1
P1: BEGIN
     DECLARE vstmt varchar(3000);
     DECLARE vname varchar(30);
     DECLARE vcounter smallint default 0;
     DECLARE at_end smallint default 0;

    DECLARE cursor1 CURSOR WITH RETURN for
        SELECT NAME FROM SYSIBM.SYSTABLES
            WHERE CREATOR = oschema ORDER BY NAME;

    DECLARE EXIT HANDLER FOR NOT FOUND
      SET at_end = 1;

    SET at_end = 0;

    OPEN cursor1;

    fetch_loop:
     REPEAT
       FETCH cursor1 INTO vname;
       SET vstmt = ‘grant ‘||permissions||’ on ‘||oschema||’.’||vname||’ to ‘||grantee;
       PREPARE s1 FROM vstmt;
       EXECUTE s1;
       SET vcounter = vcounter + 1;
       SET counter = vcounter;
     UNTIL at_end = 1
    END REPEAT fetch_loop;

CLOSE cursor1;   

END P1

SQL Server:

CREATE procedure [dbo].[Grant_TablePermission]
@permissions varchar(30),
@grantee varchar(30)
as
begin
       declare    @sqlcmd nvarchar(4000);
       declare    @tablename nvarchar(50);
       declare    @RowCount int;

      select @RowCount = 0;
      declare tablecursor insensitive cursor for
         select name from sys.tables

      open tablecursor;
      fetch tablecursor into @tablename;

      while (@@FETCH_STATUS = 0)
         begin
           select @sqlcmd = ‘GRANT ‘ + @permissions + ‘ on dbo.’+ @tablename + ‘ TO ‘+ @grantee +’;’;
           execute sp_executeSQL @sqlcmd;
           print @sqlcmd;

           set @RowCount = @RowCount + 1;

           fetch tablecursor into @tablename;
      end;

        close  tablecursor;
        deallocate tablecursor;

    SELECT ‘Number of tables permissions has been granted: ‘ + CONVERT(varchar, @RowCount)

END