Skip to main content

Microsoft SQL Server

Ensure that Microsoft SQL Server is running while installing Incentives on-premise.

Note

SQL Server Configuration Manager may not have a port correctly set. This will cause a connection fault when connecting to SQL Server.

Microsoft SQL Server database permission and component requirements

When you deploy the application on a SQL Server, Incentives does not require administrative access to the database server, but database ownership rights must be granted to access your database.

Your database may already have the right permissions. If so, you can proceed to part 2 of the installation guide. If in doubt, ask your database admin to confirm that Incentives is set up as the database owner (dbo).

It is recommended to grant Incentives dbo rights. But if this is not possible, you (or your database admin) must create a user with these minimum permissions:

  • Connect

  • Create Table

  • Create View

  • Execute

  • Select

  • Delete

  • Insert

  • Update

  • View Database State

Setting up Incentives as the database owner for Microsoft SQL Server

You must create a database user to allow the application to access the database, and then make this user the database owner.

  1. Create a database user (SPMDBUser) to be used by Incentives to access the database.

  2. Log in to SQL Server as an administrator.

  3. Connect to the database that is used by Incentives (SPMDatabase).

  4. Run this script:

    USE [SPMDatabase] 
    GO
    IF EXISTS (SELECT*FROM dbo.sysusers WHERE name=N'SPMDBUser') 
    EXEC dbo.sp_revokedbaccess N'SPMDBUser'; 
    GO
    sp_changedbowner N'SPMDBUser'

Changing the DEFAULT_SCHEMA

There are two methods to change the DEFAULT_SCHEMA for a database user to grant dbo rights.

Changing the DEFAULT_SCHEMA using a query

The default schema of the database user can be changed to the database owner using a query.

  1. Connect to the ICMDatabase with a SQL client.

  2. Log in with an administrator account.

  3. Run the following query:

    ALTER USER "ICMDBUser" WITH DEFAULT_SCHEMA dbo;

Changing the DEFAULT_SCHEMA through Microsoft SQL Server Management Studio

The default schema for the database user can be changed to database owner through SQL Server Management Studio.

  1. In SQL Server Management Studio, expand the SPMDatabase folder.

  2. Expand the Security folder.

  3. Expand the Users folder.

  4. Right-click the SPMDBUser and click Properties.

  5. Type or select dbo in the Default Schema field.

Enabling CLR integration

Varicent Incentives requires common language runtime (CLR) integration to be enabled on the SQL sever.

The CLR integration is turned off by default, so it must be enabled by using the clr enabled option of the sp_configure stored procedure in SQL Server Management Studio:

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
GO