{"id":1282,"date":"2011-12-22T04:31:31","date_gmt":"2011-12-21T22:31:31","guid":{"rendered":"http:\/\/techsatwork.com\/blog\/?p=1282"},"modified":"2016-01-07T11:31:05","modified_gmt":"2016-01-07T05:31:05","slug":"stored-proc-for-sql-server-and-db2-udb","status":"publish","type":"post","link":"https:\/\/techsatwork.com\/?p=1282","title":{"rendered":"Stored Proc for SQL Server and DB2 UDB"},"content":{"rendered":"

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.\u00c2\u00a0 Just for the fun of it, I wrote it for both DB2 UDB and SQL Server.\u00c2\u00a0 I know there are more than one way to write, but here is one way.<\/p>\n

DB2 UDB:<\/span><\/span><\/strong><\/h2>\n

CREATE PROCEDURE Grant_TablePermissions (<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0IN\u00c2\u00a0 oschema\u00c2\u00a0 varchar(20),<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0IN\u00c2\u00a0 permissions varchar(30),<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0IN\u00c2\u00a0 grantee varchar(30),<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0OUT\u00c2\u00a0 counter\u00c2\u00a0 smallint<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0)<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0DYNAMIC RESULT SETS 1<\/span>
\nP1: BEGIN<\/span>
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 DECLARE vstmt varchar(3000);<\/span>
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 DECLARE vname varchar(30);<\/span>
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 DECLARE vcounter smallint default 0;<\/span>
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 DECLARE at_end smallint default 0;<\/span><\/p>\n

\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0DECLARE cursor1 CURSOR WITH RETURN for<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0SELECT NAME FROM SYSIBM.SYSTABLES <\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0WHERE CREATOR = oschema ORDER BY NAME;<\/span><\/p>\n

\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0DECLARE EXIT HANDLER FOR NOT FOUND<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0 SET at_end = 1;<\/span><\/p>\n

\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0SET at_end = 0;<\/span><\/p>\n

\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0OPEN cursor1;<\/span><\/p>\n

\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0fetch_loop:<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0 REPEAT<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 FETCH cursor1 INTO vname;<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 SET vstmt = ‘grant ‘||permissions||’ on ‘||oschema||’.’||vname||’ to ‘||grantee;<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 PREPARE s1 FROM vstmt;<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 EXECUTE s1;<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 SET vcounter = vcounter + 1;<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 SET counter = vcounter;<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0 UNTIL at_end = 1<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0END REPEAT fetch_loop;<\/span><\/p>\n

CLOSE cursor1;\u00c2\u00a0 \u00c2\u00a0<\/span><\/p>\n

END P1<\/span><\/p>\n

SQL Server:<\/span><\/span><\/strong><\/h2>\n

CREATE procedure [dbo].[Grant_TablePermission]<\/span>
\n@permissions varchar(30),<\/span>
\n@grantee varchar(30)<\/span>
\nas<\/span>
\nbegin<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 declare\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0@sqlcmd nvarchar(4000);<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 declare\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0@tablename nvarchar(50);<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 declare\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0@RowCount int;<\/span><\/p>\n

\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0 select @RowCount = 0;<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0 declare tablecursor insensitive cursor for<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0 select name from sys.tables<\/span><\/p>\n

\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0 open tablecursor;<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0 fetch tablecursor into @tablename;<\/span><\/p>\n

\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0 while (@@FETCH_STATUS = 0)<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0 begin <\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 select @sqlcmd = ‘GRANT ‘ + @permissions + ‘ on dbo.’+ @tablename + ‘ TO ‘+ @grantee +’;’;<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 execute sp_executeSQL @sqlcmd;<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 print @sqlcmd;<\/span><\/p>\n

\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 set @RowCount = @RowCount + 1;<\/span><\/p>\n

\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 fetch tablecursor into @tablename;<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0 end;<\/span><\/p>\n

\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0close\u00c2\u00a0 tablecursor;<\/span>
\n\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0deallocate tablecursor;<\/span><\/p>\n

\u00c2\u00a0\u00c2\u00a0 \u00c2\u00a0SELECT ‘Number of tables permissions has been granted: ‘ + CONVERT(varchar, @RowCount) <\/span><\/p>\n

END<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

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.\u00c2\u00a0 Just for the fun of it, I wrote it for both DB2 UDB and SQL Server.\u00c2\u00a0 I know […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[303,3,658],"tags":[943,747,749,748,751,750,306,752,746,753],"class_list":["post-1282","post","type-post","status-publish","format-standard","hentry","category-database","category-db2","category-sql-server-2","tag-db2","tag-dynamic-sql","tag-execute","tag-grant","tag-loop","tag-sp_executesql","tag-sql-server","tag-sqlpl","tag-stored-procedure","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1282","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1282"}],"version-history":[{"count":2,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1282\/revisions"}],"predecessor-version":[{"id":1284,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1282\/revisions\/1284"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1282"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1282"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1282"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}