Securing the MONITOR database in Microsoft SQL Server

You can enhance security on SQL Server by granting the MONITOR database user only the privileges that are required to access the MONITOR database objects. You can also remove administrative grants from database scripts and place grants on specific database objects that IBM® Business Monitor requires.

Before you begin

Following the instructions in Installing the MONITOR database manually (remote database), install the MONITOR database using the information in the following steps. This procedure uses @DB_USER@ for the User name field chosen during profile creation and @SCHEMA@ for the Schema name, which were both chosen when the profile was created.

Note: You can create the database before or after the profile is created.

Procedure

Locate the DDL scripts.
- Look in the app_server_root/dbscripts/Monitor directory for the scripts that are provided when IBM Business Monitor is installed.
- If you used the profile creation function to generate scripts that include substituted variable values, look for the scripts in the output directory that you specified when you created the profile. By default, the output directory is app_server_root/profiles/profile/dbscripts/Monitor.

Remove the following lines from the createDatabase.sql file:

-- Add the monitor user to the db_owner role
EXEC sp_addrolemember 'db_owner', @DB_USER@
GO

Edit the createTables.sql file by replacing "--GRANT STMT--" with "", which removes the comment indicators for each GRANT statement, allowing the statements to run in the next step.
Run the createDatabase.sql and createTables.sql scripts:
1. Navigate to the directory that contains the createDatabase.sql script.
2. Enter the following commands:
```
sqlcmd -U username -P password -e -i createDatabase.sql
sqlcmd -U username -P password -e -i createTables.sql
```
  where
  
  username is a SQL Server administrative user.
To create the tables for the IBM Business Monitor messaging engine, complete the following steps:
1. Navigate to the directory that contains the IBM Business Monitor messaging engine scripts.
2. Enter the following command:
```
sqlcmd -U username -P password -e -i SQLServer-SibME.sql
```
  where
  
  username is a SQL Server administrative user.