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 enhances system security and allows for 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 users who are dedicated to SEI services. Using dedicated accounts makes password management easier and aligns with recommended security practices, as these accounts are not subject to the same password or authentication 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 connections for Central Point, DataSync, and any users configured via the Manage Environments and Data Sources page.

note

These scripts must be run with a user who has administrative permissions on the SQL Server.

Create administrative users

Administrative users are required to manage all SEI system components and databases with full access. SEI provides SQL scripts to automate creation of these users, ensuring appropriate permissions.

The script creates dedicated SQL Server users for each main database:

• SEI Configuration Database: Generates the SEI user.
• BI License Database: Generates the BILicense user.
• SEI Data Warehouse (SEICube): Generates the SEICube user with appropriate permissions.
• ERP database: Provides the administrative user with access to relevant schemas.

Generally, the script is designed for installations where all databases are hosted on a single server. If your environment spans multiple servers—for example, with application databases on one and warehouse data on another—run only the appropriate sections of the script on each relevant server. For instance, execute the Configuration Database and License Database commands on the application server, and use the Data Warehouse and ERP Database portions on the database server.

Run the supplied Administrative_user.sql script as a user with administrative rights on SQL Server, and follow the in-script instructions to provide usernames, passwords, and schema details suited to 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 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
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

Add users to SEI

After creating the users, you need to add them in 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. ...

Create the administration environment

1. ...

Configure the SEI configuration database and BI License database users

1. ...

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