Microsoft KB Archive/112714

= INF: Grant or Revoke Permissions for ESQL Stored Procedures =

PSS ID Number: 112714

Article Last Modified on 10/3/2003

-

The information in this article applies to:


 * Microsoft Embedded SQL for COBOL 4.2

-



This article was previously published under Q112714



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.


 * 1) 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: kbprogramming KB112714

Technology: kbAudDeveloper kbSQLEmCOBOL kbSQLServSearch kbZNotKeyword3

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.