Hello,

We are using Pega 7.4 with SQL Server 2016 and tomcat.

During my review on the compliance score I discovered some report that may need indexes.

I went to the Optimize schema page and tried to add some indexes.

I got the following message :

 Cannot find the object "DATA.PR_XXX_YYY_ZZZ_DATA_LOCAT" because it does not exist or you do not have permissions.

Last SQL: CREATE  INDEX EXCHANGEIndex ON DATA.PR_XXX_YYY_ZZZ_DATA_LOCAT ("EXCHANGE") 

I checked with database profiler and found that the base user is used, not the admin user.

Context.xml is set like this :

<Resource name="jdbc/PegaRULES"
auth="Container"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
type="javax.sql.DataSource"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://database;selectMethod=cursor;sendStringParametersAsUnicode=false"
username="base_user"
password="password"
maxActive="100"
maxIdle="30"
testWhileIdle="true"
maxWait="10000"
testOnBorrow="true"
validationQuery="SELECT 1"
jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
/>

<Resource name="jdbc/AdminPegaRULES"
auth="Container"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
type="javax.sql.DataSource"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://database;selectMethod=cursor;sendStringParametersAsUnicode=false"
username="admin_user"
password="password"
maxActive="10"
initialSize="5"
minIdle="5"
maxIdle="5"
testWhileIdle="true"
maxWait="5000"
testOnBorrow="true"
validationQuery="SELECT 1"
jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
/>

<Environment
name="prconfig/database/databases/PegaRULES/dataSourceAdmin"
value="java:comp/env/jdbc/AdminPegaRULES"
type="java.lang.String"
/>

<Environment
name="prconfig/database/databases/PegaDATA/dataSourceAdmin"
value="java:comp/env/jdbc/AdminPegaRULES"
type="java.lang.String"
/>

<Environment
name="prconfig/database/databases/PegaRULES/defaultSchema"
value="rules"
type="java.lang.String"
/>

<Environment
name="prconfig/database/databases/PegaDATA/defaultSchema"
value="data"
type="java.lang.String"
/>

As you can see admin user is set for both rules and data schema for datasourceadmin.

I don't understand why to optimizeSchema doesn't use the admin profile, is it normal?

Also, SQL rights are set as follow :

Admin :

ALTER AUTHORIZATION ON SCHEMA::[rules] TO admin_user

ALTER AUTHORIZATION ON SCHEMA::[data] TO admin_user

GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE , CREATE FUNCTION,CREATE ASSEMBLY, EXECUTE TO admin_user;

GRANT ALTER ON SCHEMA :: data to admin_user;

GRANT ALTER ON SCHEMA :: rules to admin_user;

Base:

GRANT SELECT , INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA :: data TO base_user;

Could you help to find what is the issue?

Thank you

***Edited by Moderator Marissa to update SR Details***