INF: Grant or Revoke Permissions for ESQL Stored Procedures

ID: Q112714

The information in this article applies to:

Microsoft Embedded SQL for COBOL, version 4.2

SUMMARY

When compiling Embedded SQL for COBOL (ESQL) applications using SQLACCESS option, stored procedures will be created on the SQL Server for all static SQL statements. When executing the static queries at run time, the application will call the stored procedures automatically. Often you may need to grant or revoke execution permissions on the stored procedures for security controls. However, this task may become tedious because there may be many stored procedures and all of them have very long names. This article discusses the options available.

MORE INFORMATION

Depending on your specific requirement, you may choose one of the following two options:

Using SQL scripts:

You can create an isql batch file that will select all stored procedures beginning with the program (or module) string, then use isql to grant permission on these procedures. For example, assume the ESQL/COBOL program name is myprog, then you can create a script file named
```
   getnames.sql:
      select "grant execute on ", name, " to public"
      from sysobjects
      where name like "myprog%"
 
```

Then run the following SQL scripts:

isql -Usa -Ppassword -Smyserver -igetnames.sql -ograntall.sql
isql -Usa -Ppassword -Smyserver -igrantall.sql

NOTE: You will need to modify the scripts GETNAMES.SQL and GRANTALL.SQL to remove the headers and other non Transact-SQL text, and add the necessary Transact-SQL commands such as "use yourdatabase" and "go". You can also use MS-DOS batch commands such as ECHO to automate the generation and execution of the scripts.

Using pre-compiled stored procedures:

Instead of having the ESQL/COBOL precompile service to generate the stored procedures for you, you can create the stored procedures yourself and simply call the stored procedures in place of the SQL statements. Using ESQL command EXECUTE, you can avoid a stored procedure from being compiled into another procedure when compiling the application.

Keywords : kbprg SSrvCobol SSrvProg
Version : 4.21
Platform : OS/2
Issue type :