MSSQL NIKU user setup (server roles)

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)

Or the services are not starting after a database migration

Environment

Release : All supported releases with MSSQL

Cause

This is a 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.