Creating the system and Encyclopedia volume schemas and iserver user in a pre-existing SQL Server database

Installing BIRT iHub using an alternative database : Preparing to install BIRT iHub using an alternative database : Creating the system and Encyclopedia volume schemas and iserver user in an alternative database : Creating the system and Encyclopedia volume schemas and iserver user in a pre-existing SQL Server database

The following Transact-SQL scripts provide an example of DDL statements that create the database, schema owners, and iHub application user, then grant privileges in a pre-existing SQL Server database. These steps are not necessary when adding an Encyclopedia volume to an existing schema.

The SQL Server database administrator may need to modify these SQL command examples for the specific SQL Server installation. In the commands, substitute system and schema names appropriate to your environment.

Creating a database

Connect to the SQL Server master database as a user with full system administrator, sysadmin, privileges and execute the following Transact-SQL commands to create a database named iserver:

USE master;

CREATE DATABASE iserver
COLLATE SQL_Latin1_General_CP1_CI_AS;

Any database created for iHub processing must use a case-insensitive collation, such as SQL_Latin1_General_CP1_CI_AS. The names of case-insensitive collations typically include the letters, CI.

Creating the system schema owner

In an iHub installation, the system schema owner must have the same name as the system schema. The system schema owner has all privileges on the schema used for the system data store and can grant privileges to other users. The system schema owner must be able to create database objects, such as tables and indexes.

The following commands create a user named ac_corp_system to function as the system schema owner with appropriate privileges to connect to the previously created iserver database. Connect to the iserver database as a user with full administrator privileges and execute the following SQL commands:

USE iserver;

CREATE LOGIN ac_corp_system
WITH PASSWORD = 'password';

CREATE USER ac_corp_system
FOR LOGIN ac_corp_system
WITH DEFAULT_SCHEMA = ac_corp_system;

GRANT CONNECT TO ac_corp_system;

GRANT CREATE TABLE TO ac_corp_system;

GRANT CREATE VIEW TO ac_corp_system;

GRANT CREATE FUNCTION TO ac_corp_system;

GRANT CREATE PROCEDURE TO ac_corp_system;

In the SQL Server environment, the default schema does not have to exist when creating the user. The system administrator can create the schema later.

Creating the Encyclopedia volume schema owner

In an iHub installation, the Encyclopedia volume schema owner must have the same name as the Encyclopedia volume schema. The Encyclopedia volume schema owner has all privileges on the schema used for the Encyclopedia data store and can grant privileges to other users. The Encyclopedia schema owner must be able to create database objects, such as tables and indexes.

The following commands create an Encyclopedia volume schema owner named ac_corp with appropriate privileges to connect to the previously created iserver database. Connect to the iserver database as a user with full administrator privileges and execute the following SQL commands:

USE iserver;

CREATE LOGIN ac_corp
WITH PASSWORD = 'password';

CREATE USER ac_corp
FOR LOGIN ac_corp
WITH DEFAULT_SCHEMA = ac_corp;

GRANT CONNECT TO ac_corp;

GRANT CREATE TABLE TO ac_corp;

GRANT CREATE VIEW TO ac_corp;

GRANT CREATE FUNCTION TO ac_corp;

GRANT CREATE PROCEDURE TO ac_corp;

In the SQL Server environment, the default schema does not have to exist when creating the user. The system administrator can create the schema later.

Creating the iHub application user

iHub connects to the database as an application user. The application user requires only the privileges necessary to perform basic SQL Data Manipulation Language (DML) operations, such as SELECT, INSERT, UPDATE, and DELETE. This user does not require privileges to create or modify the structure of the database.

The following SQL script provides an example of DDL statements that create the iserver user in a pre-existing SQL Server database. Connect to the iserver database as a user with full administrator privileges and execute the following SQL commands:

USE iserver;

CREATE LOGIN iserver WITH PASSWORD = 'password';

CREATE USER iserver FOR LOGIN iserver;

GRANT CONNECT TO iserver;

Creating the system schema

The system schema must have the same name as the system schema owner. The following commands create a system schema named ac_corp_system and grant ownership to the user named ac_corp_system. Connect to the iserver application database, not the SQL Server master database, as a user with full administrator privileges and execute the following commands:

USE iserver;

CREATE SCHEMA ac_corp_system AUTHORIZATION ac_corp_system;

Creating the Encyclopedia volume schema

In an iHub installation, the Encyclopedia volume schema must have the same name as the Encyclopedia volume schema owner. The following commands create an Encyclopedia volume schema named ac_corp and grant ownership to the user named ac_corp. Connect to the iserver application database, not the SQL Server master database, as a user with full administrator privileges and execute the following commands:

USE iserver;

CREATE SCHEMA ac_corp AUTHORIZATION ac_corp;