Database Server Preparation
When the Database Service component is chosen during the installation, the setup will create the Monarch Server database on the database server. You will need to have a special database user that will be used by the Monarch Server setup for database creation and under which Monarch Server will further connect to the database.
There are two levels of security that may be set for the Monarch Server database user in SQL Server. They differ in partitioning maintenance task support.
In a Monarch Server database, servicing Content Server functionality, all report-related data are partitioned by date ranges, and each partition can be managed separately — backed up, removed, joined with another partition, etc. For Monarch Server to perform these operations automatically, partition maintenance tasks were designed.
For these tasks to function properly, the Monarch Server database user will require more rights. This requirement may be considered unsecure in some environments, particularly when the database server is used for several databases and not only for Monarch Server. In this case, these rights may be assigned temporarily as the partitioning maintenance task is performed. Another option is to perform partitioning maintenance operations manually.
For setup to create the Monarch Server database — and support the partitioning maintenance task functionality — you will need the Database Creators server role to be assigned to the user.
This role will allow the user to destroy any database in the system (without reading data). Thus, it is recommended to assign this role only during the time of installation and during partitioning maintenance task operations.
To create the user, login to the database server under System Administrator (sa) user, and execute the following commands:
exec sp_addlogin 'ES_LOGIN', 'ES_PASSWORD'
exec sp_adduser 'ES_LOGIN', 'ES_USER'
exec sp_addrolemember 'db_ddladmin', 'ES_USER'
exec sp_addrolemember 'db_datareader', 'ES_USER'
exec sp_addrolemember 'db_datawriter', 'ES_USER'
exec sp_addsrvrolemember 'ES_LOGIN', 'dbcreator'
If you decide to disable the Database Creator role after the Monarch Server database is created (i.e., after installation is complete), you will need to issue the following command under System Administrator:
exec sp_dropsrvrolemember 'ES_LOGIN', 'dbcreator'
The db_owner role may be removed, and the db_executor role may be added. If the db_executor role does not exist, it may be created as follows:
CREATE ROLE db_executor
GRANT EXECUTE TO db_executor
EXEC sp_addrolemember 'db_executor','ES_USER'