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
