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
Are you setting the combatibility level when logged as SA to Ms SQL?
Does the niku user have dbowner role to you database?
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.
As the upgrade is failed, can you restore back to working version and run a health report and share with us.
Hi Suman - The health report looks clean.
So my question would be, does niku user need SA role to complete 13.3 upgrade ?
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:
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>
To apply, execute:
ALTER DATABASE <database> SET ARITHABORT ON
ALTER DATABASE <database> SET ANSI_NULLS ON
ALTER DATABASE <database> SET QUOTED_IDENTIFIER ON
MS SQL 2008: 100 To apply, execute: EXEC SP_DBCMPTLEVEL <database>, 100
MS SQL 2012: 110 To apply, execute: EXEC SP_DBCMPTLEVEL <database>, 110
Database Schema Name
The login name can be anything, but the default schema name for the login user must be niku.
Database user roles
The database user must be able to alter the Clarity-schema and otherwise own the database.
READ COMMITTED SNAPSHOT
ALTER DATABASE <database> SET READ_COMMITTED_SNAPSHOT ON
Granted to database user.
GRANT VIEW SERVER STATE to niku
REMOTE QUERY TIMEOUT
sp_configure 'remote query timeout',0
reconfigure with override
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 ?
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?
Why don't you do exactly as Suman lists above.
Thanks Suman, I tried by giving these permissions, however that did not help. Its same issue.
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.
Principal Support Engineer
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.