INF: Grant or Revoke Permissions for ESQL Stored Procedures

Last reviewed: April 29, 1997
Article ID: Q112714

The information in this article applies to:

  - Microsoft Embedded SQL version 4.21 for COBOL

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.

Additional query words:
Keywords : kbprg SSrvCobol SSrvProg
Version : 4.21
Platform : OS/2

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.