I was trying to call inv_applications_write.xml for creating new application in clarity ppm,am able to retrieve the records,but not able to insert records.am getting java.sql.SQLException: [CA Clarity][SQLServer JDBC Driver][SQLServer]The user does not have permission to perform this action. so am not sure why this is happening,does XOG client user should also exist as clarity SQL user .as the user am using to work with XOG doesnt exist for sql.Please help me on understanding this.
XOG doesn't use a special database user; it's a web (SOAP) request to the PPM server, which in turn performs actions on the database using the single user defined in the system for it.
The following possibilities are what I would look for:
1. Do an 'Action Trace' (formerly known as SQL Trace) of your XOG request, as this will aim to capture the SQL up until the point of failure and can help identify where it is going wrong.
2. Ensure you can create the equivalent objects in the PPM UI as any user - even the same PPM user.
3. Examine if there are any triggers on any of the tables relating to the object (e.g. odf_ca_inv, odf_ca_application, inv_investments, etc.), and if so, ensure they're not custom.
4. In fact, identify (by documentation or otherwise) if you have any custom triggers on your PPM schema at all.
5. Review the PPM admin guide and ensure your user has all the appropriate permissions to the database; if your DBAs have been restricting access - for example, preventing the PPM database user from being able to create new functions, procedures, and other database objects in the schema, then you will get failures of this kind.
6. Check your app-ca.log files on your PPM application server. The SQL error message, if taken from the XOG output provided to you, is likely only giving part of the picture and more of the error and its cause (such as specific SQL query text) might be included in the error message in the log files.
Using PPM Action Trace - CA Knowledge
Configure a CA PPM Database with Microsoft SQL Server