MS SQL: alter table RULES.pr_index_circumstance_def problems
Would you please assist with the correct DB permissions settings?
The DB (PEGA 7.1.2, Microsoft SQL Server 2014 Developer Edition: split-schema with the separate regular and admin users) have been created according to the official manual with all the necessary permissions and privileges.
DB and MS SQL instance privileges:
During SomeProduct.jar import procedure there weren't any errors in the Designer studio portal, and in logs there were such records:
28 Nov 2017 12:26:27,798 +03:00 [ PegaRULES-Batch-2] [ STANDARD] [ ] [ PegaRULES:07.10] ( internal.access.DatabaseImpl) ERROR Administrator@pega.com - Error occured while trying to add Index columns for an property
com.pega.pegarules.pub.database.DatabaseException: Database-General Problem executing SQL to update database schema 1088 S00013 Cannot find the object "pr_index_circumstance_def" because it does not exist or you do not have permissions.
DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot find the object "pr_index_circumstance_def" because it does not exist or you do not have permissions.
| SQL Code: 1088 | SQL State: S00013
SQL: alter table RULES.pr_index_circumstance_def add "GlobalStatus" VARCHAR(32)
Nevertheless, such table is presented in RULES schema:
As I may understand, we need «ALTER TABLE» privilege for such situation, but MS SQL haven’t «GRANT ALTER TABLE», just «GRANT ALTER» (it is possible to apply it for the concrete DB-object), which have more huge privileges than «GRANT ALTER TABLE»:
GRANT ALTER TO [PegaAdminUser]
Role db_owner for PegaAdminUser – this is the delegation of redundant rights:
ALTER ROLE [db_owner] ADD MEMBER [PegaAdminUser]
Such grant cannot be applied:
GRANT ALTER ON [RULES].[pr_index_circumstance_def] TO [PegaAdminUser]
Because of I already have an ALTER privileges for PegaAdminUser (owner of RULES schema):
"Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself."