Clarity

Expand all | Collapse all

13.3 SQL Server pre-upgrade fail for sysadmin role

Jump to Best Answer
  • 1.  13.3 SQL Server pre-upgrade fail for sysadmin role

    Posted 08-26-2015 03:52 PM

    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



  • 2.  Re: 13.3 SQL Server pre-upgrade fail for sysadmin role

    Posted 08-26-2015 04:01 PM

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

    Does the niku user have dbowner role to you database?



  • 3.  Re: 13.3 SQL Server pre-upgrade fail for sysadmin role

    Posted 08-27-2015 06:45 AM

    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.



  • 4.  Re: 13.3 SQL Server pre-upgrade fail for sysadmin role

    Posted 08-27-2015 02:14 AM

    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



  • 5.  Re: 13.3 SQL Server pre-upgrade fail for sysadmin role

    Posted 08-27-2015 06:45 AM

    Hi Suman - The health report looks clean.

     

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



  • 6.  Re: 13.3 SQL Server pre-upgrade fail for sysadmin role

    Posted 08-27-2015 06:49 AM

    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



  • 7.  Re: 13.3 SQL Server pre-upgrade fail for sysadmin role

    Posted 08-27-2015 07:34 AM

    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 ?



  • 8.  Re: 13.3 SQL Server pre-upgrade fail for sysadmin role

    Posted 08-27-2015 01:19 PM

    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?



  • 9.  Re: 13.3 SQL Server pre-upgrade fail for sysadmin role

    Posted 08-27-2015 10:37 AM

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



  • 10.  Re: 13.3 SQL Server pre-upgrade fail for sysadmin role

    Posted 08-27-2015 01:06 PM

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



  • 11.  Re: 13.3 SQL Server pre-upgrade fail for sysadmin role

    Posted 08-27-2015 01:32 PM

    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



  • 12.  Re: 13.3 SQL Server pre-upgrade fail for sysadmin role
    Best Answer

    Posted 08-27-2015 04:36 PM

    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.