MSSQL NIKU user setup (server roles)
search cancel

MSSQL NIKU user setup (server roles)

book

Article ID: 139254

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

Best practices on setting up a SQL Server permissions and server roles for niku user

Not setting the permissions right may lead to upgrade issues as below: 

Clarity upgrade on MSSQL fails with errors such as the following: 

  • 10/25/19 11:32 AM (ExecTask) Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]Object is invalid. Extended properties are not permitted on 'CLARITYUSER.DWH_CFG_SETTINGS.USE_DATAPUMP', or the object does not exist.
  • 10/28/19 9:34 AM (SQLTask) Failed to execute:  select 1 dwh_exists from dual where exists (select * from sysobjects where name='DWH_SETTINGS')
  • 10/28/19 9:34 AM (UnknownElement) Task "nsql" finished with error.
  • C:\Installs\Clarity\checkinstall\scripts\imports\dwh-utils.xml:68: java.sql.SQLSyntaxErrorException: [CA Clarity][SQLServer JDBC Driver][SQLServer]Invalid object name 'dual'.
  • at com.niku.tools.taskdefs.SQLTask.execute(SQLTask.java:602)
  • Exception in thread "main" com.niku.union.config.ConfigurationException: Could not read configuration from database
    at com.niku.union.config.ConfigurationManager.fetchConfigDatabaseDocument(ConfigurationManager.java:5287)
    at com.niku.union.config.ConfigurationManager.main(ConfigurationManager.java:6438)
    Caused by: java.sql.SQLSyntaxErrorException: [CA Clarity][SQLServer JDBC Driver][SQLServer]Invalid object name 'CMN_CONFIG'.

Or the services are not starting after a database migration

Environment

Release : All supported releases with MSSQL

Cause

This is a database permission issue. 

Resolution

  1. Connect to SQL Management Studio - Security - Logins - Properties and check the login user permissions to NIKU and PPM_DWH databases. 
    • They should be set up as db_owner
    • They should not be sysadmin or have any other roles than that
  2. If User Mapping is used, you should see mapping as follows (CLARITYUSER example of MSSQL login user):
    • Database User Default Schema
    • CLARITYDWH CLARITYUSER ppm_dwh
    • CLARITYPPM CLARITYUSER niku
  3. The default schema for ppm_dwh and niku should be mapped to CLARITYUSER
  4. Make sure the GRANT VIEW SERVER STATE is granted to the login as well. 
  5. Also make sure you grant the below permissions:
    • ARITHABORT ON
    • ANSI_NULLS ON
    • QUOTED_IDENTIFIER ON
    • READ_COMMITTED_SNAPSHOT ON