New schema holder fails with error " Could not find stored procedure '_Constraint_Info'. "

Generating schema holder with Create DataServer Schema utility for DataServer for Microsoft SQL Server fails with error " Could not find stored procedure '_Constraint_Info'. "

Jan 18, 2021
Knowledge

Title

URL Name

000052426

Article Number

000111739

Environment

Product: OpenEdge Version: 11.x, 12.x OS: Windows Other: DataServer for Microsoft SQL Server

Question/Problem Description

Generating a schema holder fails with error: " Could not find stored procedure '_Constraint_Info'. ".

Creating schema holder fails with error: " The specified schema name <dbo> either does not exist or you do not have permission to use it. ".

Generating a schema holder with Create DataServer Schema utility for DataServer for Microsoft SQL Server fails with error: " The EXECUTE permission was denied on the object _Constraint_Info , database <test>, schema <dbo>.. "

ABL client gets error 1494.

Steps to Reproduce

Clarifying Information

SQL Server user specified for the schema pull operation has limited privileges on the database.

Schema pull operation works using the same SQL Server user permissions in OpenEdge 10.

When using the SQL Server 'sa' user the same schema pull operation succeeds in OpenEdge 11

Error Message

[Microsoft][SQL Server Native Client 11.0][SQL Server]Could not find stored procedure '_Constraint_Info'. [Microsoft][SQL Server Native Client 11.0][SQL Server]The specified schema name "dbo" either does not exist or you do not have permission to use it. [Microsoft][SQL Server Native Client 11.0][SQL Server]CREATE TABLE permission denied in database '<database name>'. [Microsoft][SQL Server Native Client 11.0][SQL Server]The EXECUTE permission was denied on the object '_Constraint_Info', database '<database name>', schema 'dbo'. <program> --Could not find file <name> in database <db>. (1494)

Defect Number

Enhancement Number

Cause

OpenEdge 11.0 provides support for foreign constraints in the DataServer. The Create DataServer Schema process requires the _Constraint_Info stored procedure to be created in the SQL Server database. The current SQL Server user performing the pull does not has sufficient privileges to complete this operation.

Resolution

Before generating the schema holder, grant the following additional privileges to the SQL Server user:

GRANT EXECUTE TO test;

GRANT CREATE PROCEDURE TO test;

GRANT ALTER ON SCHEMA::dbo TO test;

GRANT CREATE TABLE TO test;

Generate the Schema Holder using the DataServer Schema utility for DataServer for Microsoft SQL Server.

Once the new schema holder has been generated, revoke privileges that are not required by this user for normal DataServer operations:

REVOKE EXECUTE FROM test;

REVOKE CREATE PROCEDURE FROM test;

REVOKE ALTER ON SCHEMA::dbo FROM test;

REVOKE CREATE TABLE FROM test;

These privileges will need to be granted again when future modifications need to be made to the schema holder at a later date, e.g. a re-pull or update. Strictly, a dedicated user should be used for these operations.

Workaround

Notes

References to Other Documentation:

Use Microsoft SQL Server DataServer: Configuring the DataServer, Creating a schema holder, Authorization and authentication - Permissions for creating schema holder with constraints https://docs.progress.com/bundle/openedge-microsoft-sql-dataserver/page/Permissions-for-creating-schema-holder-with-constraints.html

Progress Article:
"What are the minimum security privileges required for a user to create schema holder from MS SQL Server?"

Keyword Phrase

Last Modified Date

1/18/2021 11:49 AM

New schema holder fails with error " Could not find stored procedure '_Constraint_Info'. "

Files(0)

Was this article helpful?

Was this article helpful?

New schema holder fails with error " Could not find stored procedure '_Constraint_Info'. "

Information

Related Articles