SQL Server Error When Trying to Create a Department

Products

• Clarity PPM On Premise

Issue/Introduction

ERROR 2021-01-20 22:14:28,212 [http-nio-82-exec-34] niku.xql2 (clarity:admin:xxxxx:revmgr.insertDepartment) Internal Processing exception
com.niku.union.persistence.PersistenceException: 
SQL error code: 300
Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]VIEW SERVER STATE permission was denied on object 'server', database 'master'.
SQL error code: 297
Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]The user does not have permission to perform this action.
Executed:
 DELETE from prj_obs_associations WHERE unit_id = ? AND table_name = ? and record_id = ? 
Derived from statement:
<?xml version="1.0" encoding="UTF-8"?>
<statement location="financialProperties.xml" id="revmgr.deleteOldPrjObsAssoc" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" inputSource="none" xmlns="http://schemas.niku.com/2002/pmd">
  <sql dbVendor="all">
    <text>
        DELETE from prj_obs_associations WHERE unit_id = ? AND table_name = ? and record_id = ?
      </text>
    <param name="obs_unit_id" type="long" direction="IN" expressionListDelimiter=","/>
    <param name="table_name" type="string" direction="IN" expressionListDelimiter=","/>
    <param name="projectId" type="long" direction="IN" expressionListDelimiter=","/>
  </sql>
</statement>

Cause

The security user associated with the niku database does not have permissions.

Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]VIEW SERVER STATE permission was denied on object 'server', database 'master'.

Resolution

The security user 

In the following example, 'tt' is the security user having access to the 'Clarity1561' database.
1. Right-click the database(Clarity1561)
2. Set the dropdown = master
3. Run query: grant view server state to tt