Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Adilaxmi
Starting Member
2 Posts |
 Posted - 2005-08-12 : 08:36:33
|
| Hi all,When the sample integrating application Develop.cnk was integrated to great plains we found out that the following stored procedure was not created. Please go through the following code. The control is successfully getting in to the if block. Once getting in, the code is unable to create the stored procedure called IGautogrant. Other tables scripts use this script to grant access. Due to this some of the tables are not being created.if status = 0 then {Build SQL Statements} SQL_Statements = "create procedure IGAutoGrant @tablename char(150) output as set nocount on "; SQL_Statements = SQL_Statements + "DECLARE @command varchar(255) SELECT @command = "; SQL_Statements = SQL_Statements + "'grant SELECT,INSERT,DELETE,UPDATE on '+rtrim(@tablename) +' to DYNGRP' "; SQL_Statements = SQL_Statements + "EXEC (@command) DELETE FROM IGautotemp SELECT @command = "; SQL_Statements = SQL_Statements + QUOTE + "insert into IGautotemp select name from sysobjects where name like 'zDP_" + QUOTE; SQL_Statements = SQL_Statements + "+rtrim(@tablename) + " + QUOTE + "%'" + QUOTE + "EXEC (@command) DECLARE TheCursor CURSOR FOR "; SQL_Statements = SQL_Statements + "select 'grant EXECUTE on '+ rtrim(name) + " + QUOTE + " to DYNGRP" + QUOTE + " from IGautotemp "; SQL_Statements = SQL_Statements + "OPEN TheCursor WHILE @@FETCH_STATUS = @@FETCH_STATUS BEGIN FETCH NEXT FROM TheCursor INTO @command "; SQL_Statements = SQL_Statements + "IF @@FETCH_STATUS = -2 CONTINUE IF @@FETCH_STATUS = -1 BREAK EXEC (@command) END "; SQL_Statements = SQL_Statements + "CLOSE TheCursor DEALLOCATE TheCursor ; SET NOCOUNT off ; "; {Execute the SQL statements} status = SQL_Execute(SQL_connection, SQL_Statements); Please let me know what this code is exactly doing. I would appreciate your help.laxmi s.a.laxmi |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-12 : 16:01:51
|
Basically, it is creating the following SQL command (which creates a stored procedure) and executes it.create procedure IGAutoGrant @tablename char(150) output as set nocount on DECLARE @command varchar(255) SELECT @command = 'grant SELECT,INSERT,DELETE,UPDATE on '+rtrim(@tablename) +' to DYNGRP' EXEC (@command) DELETE FROM IGautotemp SELECT @command = ' insert into IGautotemp select name from sysobjects where name like ''zDP_'' +rtrim(@tablename) + ''%''' EXEC (@command) DECLARE TheCursor CURSOR FOR select 'grant EXECUTE on '+ rtrim(name) + ' to DYNGRP' from IGautotemp OPEN TheCursor WHILE @@FETCH_STATUS = @@FETCH_STATUS BEGIN FETCH NEXT FROM TheCursor INTO @command IF @@FETCH_STATUS = -2 CONTINUE IF @@FETCH_STATUS = -1 BREAK EXEC (@command) END CLOSE TheCursor DEALLOCATE TheCursor SET NOCOUNT off The stored procedure grants SELECT, INSERT, UPDATE, and DELETE permissions on tables to the user/group named DYNGRP and EXECUTE permissions to stored procedures which are identified as having zDP_ in their name.---------------------------EmeraldCityDomains.com |
 |
|
|
|
|
|
|
|
Develop.cnk