Create SQL Server Users

For secure access to SEI databases, it is strongly recommended to create dedicated SQL Server user accounts instead of using the default sa credentials or other administrative accounts. This approach improves security and provides more granular control over database access.

SEI provides SQL scripts to create users with either administrative or limited permissions. These scripts must be executed by a user with administrative rights on the SQL Server. After installing SEI, run the appropriate script to create service accounts for SEI. Dedicated accounts simplify password management and align with recommended security practices, because they are not subject to the same requirements as the sa account or Windows-based users.

Dedicated SQL Server users should have only the permissions necessary for their intended use. This applies to accounts for Central Point, DataSync, and any users configured via the Environments and Data Sources page.

note

Run all scripts with a user who has administrative permissions on SQL Server.

Main steps

Step	Description
Create administrative users	Administrative users manage all SEI system components and databases with full access. SEI provides SQL scripts to automate creation of these users, ensuring proper permissions.
Create users with minimum permissions	Users with restricted rights can access only the required schemas and operations for reporting or integration purposes.
Additional access configuration	Permission adjustments using GRANT or DENY statements to fine-tune access for specific schemas or tables.

Create administrative users

The script creates dedicated users for each key database:

• SEI Configuration Database: Creates the SEI user.
• BI License Database: Creates the BILicense user if you are running a version prior to the 2026 Release 1.
• SEI Data Warehouse (SEICube): Creates the SEICube user with necessary permissions.
• ERP database: Provides the administrative account with access to relevant schemas.

The script is designed for installations where databases are hosted on a single server. If your environment spans multiple servers (for example, application databases on one server and warehouse data on another), run only the relevant sections of the script on each respective server.

Run the supplied Administrative_user.sql script as an administrator on SQL Server, and follow the script instructions to provide usernames, passwords, and schema details for your environment.

note

The sample script is based on a Sage X3 environment. Adjust schema names and folder parameters to match your own ERP setup.

/*
  Script to create SQL Server users with administrative permissions.
  Run this script as a user with administrative privileges on the SQL Server.
  Instructions:
    - Replace "userWithAdministrativePermissions" with the desired SQL user name.
    - Specify a secure password for the login.
    - Replace "NECTARI_CUSTOM_SCHEMA" with the actual Nectari/SEI schema name.
    - Replace "X3FOLDER" with the correct ERP folder name used by Nectari/SEI.
    - Adjust database and schema names for your environment as needed.
  Note:
    - You can restrict GRANTs and role membership further if tighter security is required.
    - For environments with distributed components, run only the parts relevant to the local databases.
*/

CREATE LOGIN userWithAdministrativePermissions WITH PASSWORD = '' -- Creates server-level login

-- Nectari Data Warehouse 
use [NectariCube] 
CREATE USER userWithAdministrativePermissions FOR LOGIN userWithAdministrativePermissions
ALTER ROLE db_ddladmin add member userWithAdministrativePermissions DDL/admin rights --DDL/admin rights
ALTER ROLE db_datareader add member userWithAdministrativePermissions -- SELECT permissions on all tables
ALTER ROLE db_datawriter add member userWithAdministrativePermissions -- INSERT, UPDATE, DELETE on all tables
GRANT EXEC to userWithAdministrativePermissions -- Execute stored procedures

-- ERP database (e.g., Sage X3)
use [x3]
CREATE USER userWithAdministrativePermissions FOR LOGIN userWithAdministrativePermissions
GRANT ALTER on SCHEMA :: NECTARI_CUSTOM_SCHEMA to userWithAdministrativePermissions -- Full ALTER rights on custom schema
GRANT SELECT,INSERT,UPDATE,DELETE,EXEC on SCHEMA :: NECTARI_CUSTOM_SCHEMA to userWithAdministrativePermissions -- Full DML and EXEC on custom schema
GRANT SELECT on SCHEMA :: X3FOLDER to userWithAdministrativePermissions -- Read-only access to all tables of ERP folder
GRANT CREATE TABLE to userWithAdministrativePermissions -- Allows table creation
GRANT ALTER on SCHEMA :: X3FOLDER to userWithAdministrativePermissions -- Allows trigger creation

-- Configuration Database
use [NECTARI] 
CREATE USER userWithAdministrativePermissions FOR LOGIN userWithAdministrativePermissions
ALTER ROLE db_ddladmin add member userWithAdministrativePermissions -- DDL/admin rights
ALTER ROLE db_datareader add member userWithAdministrativePermissions -- SELECT permissions
ALTER ROLE db_datawriter add member userWithAdministrativePermissions -- INSERT, UPDATE, DELETE

-- License Database (Only for versions prior to 2026 Release 1)
use [BILicense] 
CREATE USER userWithAdministrativePermissions FOR LOGIN userWithAdministrativePermissions
ALTER ROLE db_ddladmin add member userWithAdministrativePermissions -- DDL/admin rights
ALTER ROLE db_datareader add member userWithAdministrativePermissions -- SELECT permissions
ALTER ROLE db_datawriter add member userWithAdministrativePermissions -- INSERT, UPDATE, DELETE

Create users with minimum permissions

For scenarios where full database access is not required, SEI supports SQL Server users with restricted privileges. Users with minimum permissions have read (SELECT) access to all tables in the required databases, and can be further restricted as needed. This setup is useful for reporting users or integrations that only require limited data access.

The provided script grants these users access to the SEI Data Warehouse (NectariCube) and the ERP database (such as Sage X3) schemas. The script can be adjusted to exclude access to specific tables by applying one or more DENY statements.

Run the MinimumPermissions_user.sql script with an account that has administrative permissions on the SQL Server. Follow the instructions in the script to provide the correct user names, passwords, and schema details for your environment.

note

The sample script provided is based on a Sage X3 environment. Be sure to adjust schema names and folder parameters to match your own ERP database setup.

/*
  Script to create restricted users with minimum permissions.
  Run this script as a user with administrative privileges on the SQL Server.
  Instructions:
    - Replace "userWithMinimumPermissions" with your desired SQL user name.
    - Specify a password for the login.
    - Replace "NECTARI_CUSTOM_SCHEMA" with your Nectari/SEI schema name.
    - Replace "X3FOLDER" with your ERP folder name used by Nectari/SEI.
    - Uncomment optional statements as needed.
    - Add DENY statements at the end to block access to specific tables.
*/

CREATE LOGIN userWithMinimumPermissions WITH PASSWORD = '' -- Creates a server-level login

-- Data Warehouse 
use [NectariCube] 
CREATE USER userWithMinimumPermissions FOR LOGIN userWithMinimumPermissions
ALTER ROLE db_datareader add member userWithMinimumPermissions -- SELECT permission
ALTER ROLE db_datawriter add member userWithMinimumPermissions -- INSERT/UPDATE/DELETE
GRANT EXEC to userWithMinimumPermissions -- Execute stored procedures
-- ALTER ROLE db_ddladmin ADD MEMBER userWithMinimumPermissions;   -- Optional: Add DDL rights if required for table creation via stored procs

-- ERP database (e.g., Sage X3)
use [x3]
CREATE USER userWithMinimumPermissions FOR LOGIN userWithMinimumPermissions
GRANT SELECT,INSERT,UPDATE,DELETE,EXEC on SCHEMA :: NECTARI_CUSTOM_SCHEMA to userWithMinimumPermissions -- Full DML on custom schema
GRANT SELECT on SCHEMA :: X3FOLDER to userWithMinimumPermissions -- Read-only access to all tables in the ERP folder
-- GRANT ALTER ON SCHEMA::NECTARI_CUSTOM_SCHEMA TO userWithMinimumPermissions; -- Optional: Full ALTER on custom schema (rarely needed)

-- Add DENY statements below to block access to sensitive tables:
DENY SELECT on X3FOLDER.TABLENAME to userWithMinimumPermissions -- Example: blocks SELECT on a specific table

Aditionnal access configuration

Grant access to a new database schema

When a new database schema is added, users with minimum permissions will need explicit access to it. To grant this access, run the GRANT SELECT on SCHEMA command within the ERP database context for each new schema. For example, to give user produser1 read access to a new schema called TEST:

GRANT SELECT on SCHEMA :: TEST to produser1

Deny access to a database schema or table

To restrict access and ensure a user with minimum permissions cannot read certain tables, use the DENY SELECT command within the ERP database script. For example, the following statements deny produser1 access to three specific tables:

DENY SELECT on SEED.GACCENTRYD to produser1
DENY SELECT on SEED.GACACCOUNT to produser1
DENY SELECT on SEED.GACCDUDATE to produser1

Grant alter access and edit rights

Granting additional privileges allows users to edit specific SEI objects if needed. To enable a user to modify SEI schema objects, use the GRANT ALTER on SCHEMA command. For instance, to allow administrative user adminuser1 to alter custom schema objects:

GRANT ALTER on SCHEMA :: NECTARI_CUSTOM_SCHEMA to adminuser1

To allow a user with minimum permissions to insert, update, and delete records in the Data Warehouse (Cube), use the ALTER ROLE command from the SEI Data Warehouse script. For example, to provide these rights to produser1:

ALTER ROLE db_datawriter add member produser1

note

Need to add users after SEI is installed using the Env. & Data Sources page in Administration.

Add users to SEI

Create the production environment

1. Log in to SEI.
2. In the navigation panel, select the gear icon to open Administration.
3. Select Env. & Data Sources on the left panel.
4. In the Environments panel, click the + icon.
5. In Environment Name, enter Production.
6. In Abbreviated Name, enter Prod.
7. Choose the color for the environment. Use the color picker and select Apply, or enter a HEX code manually.
8. Click Confirm.

Create the administration environment

1. In the Environments panel, click the + icon again.
2. In Environment Name, enter Administration.
3. In Abbreviated Name, enter Admin.
4. Choose the color for the environment. Use the color picker and select Apply, or enter a HEX code manually.
5. Click Confirm.

Set database credentials in Central Point Configurator

1. Connect to the Central Point Configurator.
2. In the Data Source and License Data Source sections, enter the administrative database username and password.
3. Click Update configuration to save your changes.