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