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

About rpillai

I am a technology enthusiasts and love to work with databases and other technology. Learning new things everyday and don't think the path ever ends ...
This entry was posted in database, db2, sql server and tagged , , , , , , , , , . Bookmark the permalink.