13.3 SQL Server pre-upgrade fail for sysadmin role

[kumu]

Hello,

 

I am trying to upgrade WIN / SQL Server 2008 instance fro 13.2 to 13.3, where pre-upgrade fails while checking/setting compatibility with permission error as below. I have already set the compatibility = 100 as needed, still its going for check and failing. On other way have assigned sysadmin role to niku user, however in that case script could not run any database SQL with out append it schema name (for ex. select 1 from cmn_config; [fail]. Script expects: select 1 from niku.cmn_config;)

 

Any ideas in this case ?

 

Error- java.sql.SQLException: [CA Clarity][SQLServer JDBC Driver][SQLServer]Only members of the sysadmin role or the database owner may set the database compatibility level.

 

DECLARE @V_DB_NAME sysname,@V_PRODUCT_VERSION  INTEGER

SELECT @V_DB_NAME= (SELECT db_name(db_id()))

    SELECT @V_PRODUCT_VERSION= (SELECT (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER)))

SET IMPLICIT_TRANSACTIONS OFF

 

IF @V_PRODUCT_VERSION = 10 --SQL Server

EXEC sp_dbcmptlevel @dbname=@V_DB_NAME, @new_cmptlevel=100

 

IF @V_PRODUCT_VERSION = 11

EXEC sp_dbcmptlevel @dbname=@V_DB_NAME, @new_cmptlevel=110

SET IMPLICIT_TRANSACTIONS ON

[Deactivated User]

Are you setting the combatibility level when logged as SA to Ms SQL?

Does the niku user have dbowner role to you database?

[kumu]

Hi Urmas - Before starting upgrade I am setting combatibility level using SA login.

 

If I assign SA role to niku user it star seeing all the schema and then it cannot directly run SQLs, it always need schema name as updated earlier.

[Suman Pramanik]

HI Kumu002.

 

As the upgrade is failed, can you restore back to working version and run a health report and share with us.

 

Regards

Suman Pramanik

[kumu]

Hi Suman - The health report looks clean.

 

So my question would be, does niku user need SA role to complete 13.3 upgrade ?

[Suman Pramanik]

It doesn't below are the rights required at database

 

Associate the imported database with your organization's SQL server security user by running the following pl/sql as the sa user:

USE niku

ALTER USER niku WITH LOGIN=<your security user>

6. To grant the VIEW SERVER STATE to the <your security user> user, use the following command:

GRANT VIEW SERVER STATE to <your security user>

 

Option

Value

ARITHABORT

ANSI NULLS

QUOTED IDENTIFIER

ON

To apply, execute:

ALTER DATABASE <database> SET ARITHABORT ON

ALTER DATABASE <database> SET ANSI_NULLS ON

ALTER DATABASE <database> SET QUOTED_IDENTIFIER ON

Compatibility level

MS SQL 2008: 100 To apply, execute: EXEC SP_DBCMPTLEVEL <database>, 100

MS SQL 2012: 110 To apply, execute: EXEC SP_DBCMPTLEVEL <database>, 110

 

Option

Value

Database Schema Name

niku

The login name can be anything, but the default schema name for the login user must be niku.

Database user roles

db_owner

The database user must be able to alter the Clarity-schema and otherwise own the database.

READ COMMITTED SNAPSHOT

ON

To apply, execute:

ALTER DATABASE <database> SET READ_COMMITTED_SNAPSHOT ON

VIEW_SERVER_STATE

Granted to database user.

To apply, execute:

GRANT VIEW SERVER STATE to niku

REMOTE QUERY TIMEOUT

sp_configure 'remote query timeout',0

reconfigure with override

[kumu]

Hi - The database owner is "clarity" which has all the rights(SA). The database and user we use is "niku", if I give SA role to niku it needs schema name, where scripts fails, if SA role is not given then it fails at compatibility test in preupgrade script.

 

I am stuck, does any one have seen such situation. Can we ignore/comment the compatibility as I am already setting it before starting upgrade ?

[Nick Darlington]

Your database should be having a user 'niku' (with default schema 'niku') who is also the dbowner, and this user should be the one that your MSSQL 'login' (as defined in the Clarity CSA) is mapped to use when connecting to your Clarity database.

 

It sounds confusing to have another owner called 'clarity' for the database.  How/why was it setup that way?

[Deactivated User]

Why don't you do exactly as Suman lists above.

[kumu]

Thanks Suman, I tried by giving these permissions, however that did not help. Its same issue.

[Nika Hadzhikidi]

Hi Kumu,

 

 

You should NOT grant sysadmin role to niku user, this is going to be an issue for you later in Clarity. We have seen instances in which this role was conflicting.

 

From the error above it looks like it fails because your PPM user is not the dbowner of niku database. From your other comments I see you have set up another account "clarity" to be the owner. This is the issue. Please use only niku user, and set it up in SQL Management studio to be the database owner. Same user should be specified in CSA to connect to Clarity. This is requirement as per our Installation Guide. Ensure it all works for Clarity, then you can go ahead and start your upgrade.

 

Please let me know how it goes.

 

Kind Regards

 

Nika Hadzhikidi

CA Technologies

Principal Support Engineer

[kumu]

OK, was able to make it move, added db_owner role to niku user though security > logins for niku database. That did the trick.

 

Thanks for the inputs... Appreciated.