Using a pre-created database for Smart IT

If you have database access restrictions and cannot allow the BMC MyIT and BMC Remedy with Smart IT (Smart IT) installer to create a database, you can create a pre-existing database.

Databases

BMC MyIT and Smart IT require the following database schemas:

You can replace schemaName with SmartIT or any other identifier that you prefer. BMC recommends that you keep "_business" and "_system" as part of these schema names so that you can easily identify the data in the tables. During installation, you will be able to specify these schemas.

To create a Microsoft SQL database

1. Log on to your Microsoft SQL Server, and create an empty database. 
   Example: SmartIT

2. Create users for the business schema, system schema, and (optionally) Openfire.
   Example: SmartIT_business, SmartIT_system, openfire 
3. Assign the users to the database you created in step 1.
4. Specify the following permissions for the users:

   • grant connect

   • create schema

   • create table

     Examples

     grant connect, create schema, create table to SmartIT_business;
     grant connect, create schema, create table to SmartIT_system;
     grant connect, create schema, create table to openfire;
     
     

     Optionally, you can run the following script directly in Management Studio:

     USE master
     GO
     CREATE DATABASE SmartIT
     GO
     use SmartIT
     GO
     CREATE LOGIN openfire WITH PASSWORD = N'SmartIT',DEFAULT_DATABASE=[SmartIT], CHECK_EXPIRATION=off,CHECK_POLICY=off
     GO
     CREATE LOGIN SmartIT_System WITH PASSWORD = N'SmartIT', DEFAULT_DATABASE=[SmartIT], CHECK_EXPIRATION=off,CHECK_POLICY=off
     GO
     CREATE LOGIN SmartIT_Business WITH PASSWORD = N'SmartIT', DEFAULT_DATABASE=[SmartIT], CHECK_EXPIRATION=off,CHECK_POLICY=off
     GO
     CREATE USER openfire from LOGIN openfire
     GO
     CREATE USER SmartIT_System from LOGIN SmartIT_System
     GO
     CREATE USER SmartIT_Business from LOGIN SmartIT_Business
     GO
     CREATE SCHEMA openfire AUTHORIZATION openfire
     GO
     CREATE SCHEMA SmartIT_System AUTHORIZATION SmartIT_System
     GO
     CREATE SCHEMA SmartIT_Business AUTHORIZATION SmartIT_Business
     GO
     ALTER USER openfire WITH DEFAULT_SCHEMA = openfire
     GO
     ALTER USER SmartIT_System WITH DEFAULT_SCHEMA = SmartIT_System
     GO
     ALTER USER SmartIT_Business WITH DEFAULT_SCHEMA = SmartIT_Business
     GO
     GRANT CONNECT,CREATE TABLE,CREATE SCHEMA TO openfire
     GO
     GRANT CONNECT,CREATE TABLE,CREATE SCHEMA TO SmartIT_Business
     GO
     GRANT CONNECT,CREATE TABLE,CREATE SCHEMA TO SmartIT_System
     GO

5. On your installation worksheet, write the database name, users, and passwords.

When you run the installer, instead of using the sa credentials, you can use the business credentials.

To create an Oracle database

1. Create Business, System, and (optionally) OpenFire users before running the installer.
   

2. Specify the following permissions for the users:

   • create sequence

   • create table

   • create view

   • create session

   • alter session

   You can use the following commands to create the users and grant the needed roles:

   create user SmartIT_Business identified by SmartIT default tablespace
   USERS temporary tablespace TEMP quota unlimited on USERS;
   
   create user SmartIT_System identified by SmartIT default tablespace USERS
   temporary tablespace TEMP quota unlimited on USERS;
   
   create user openfire identified by openfire default tablespace USERS
   temporary tablespace TEMP quota unlimited on USERS;
   
   grant create sequence, create table, create view, create session, alter
   session, to SmartIT_Business;
   
   grant create sequence, create table, create view, create session, alter
   session, to SmartIT_System;
   
   grant create sequence, create table, create view, create session, alter
   session, to openfire;

3. On your installation worksheet, write the database name, users, and passwords.

When you run the installer, select the Use Pre-created DB option in the RDBMS Selection window. When you select this option, you are not prompted to enter system credentials.

To pre-create database for Windows authentication

1. Create three users in your domain.
   For example,<Domain name>\SmartIT_Business, <Domain name>\SmartIT_System, <Domain name>\openfire.

2. Pre-create the database using the following script:

   Run the script:
   USE master
   GO
   CREATE DATABASE SmartIT
   GO
   use SmartIT
   GO
   CREATE LOGIN [<Domain name>\ SmartIT_Business] FROM WINDOWS;
   GO 
   CREATE USER [<Domain name>\SmartIT_Business] from LOGIN [<Domain name>\SmartIT_Business]
   GO
   CREATE SCHEMA SmartIT_Business AUTHORIZATION [<Domain name>\SmartIT_Business]
   GO
   ALTER USER [<Domain name>\SmartIT_Business] WITH DEFAULT_SCHEMA = SmartIT_Business
   GO
   GRANT CONNECT,CREATE TABLE,CREATE SCHEMA TO [<Domain name>\SmartIT_Business]
   GO
   CREATE LOGIN [<Domain name>\ SmartIT_System] FROM WINDOWS;
   GO
   CREATE USER [<Domain name>\SmartIT_System] from LOGIN [<Domain name>\SmartIT_System]
   GO
   CREATE SCHEMA SmartIT_Sys AUTHORIZATION [DSL.BMC.COM\SmartIT_Sys]
   GO
   ALTER USER [<Domain name>\SmartIT_System] WITH DEFAULT_SCHEMA = SmartIT_System
   GO
   GRANT CONNECT,CREATE TABLE,CREATE SCHEMA TO [<Domain name>\SmartIT_System]
   GO
   CREATE LOGIN [<Domain name>\openfire] FROM WINDOWS;
   GO 
   CREATE USER [<Domain name>\openfire] from LOGIN [<Domain name>\openfire]
   GO
   CREATE SCHEMA openfire AUTHORIZATION [<Domain name>\openfire]
   GO
   ALTER USER [<Domain name>\openfire] WITH DEFAULT_SCHEMA = openfire
   GO
   GRANT CONNECT,CREATE TABLE,CREATE SCHEMA TO [<Domain name>openfire]
   GO

3. On your installation worksheet, write the database name, users, and passwords.
4. During installation select the pre-created db option.
5. Enter user name as <Domain name>\SmartIT_Business, <Domain name>\SmartIT_System, <Domain name>\openfire along with the domain passwords.