Create a Microsoft SQL Server database for CONTROL
Create a Microsoft SQL Server database for CONTROL
Microsoft SQL Server Database Creation
Create the SQL Server CNTADM account
CONTROL Database Initialization/Restore
This document describes the steps necessary to create a database for CONTROL in Microsoft SQL Server. These instructions apply to MS SQL Server versions 2005, 2008, 2008 R2, 2012 and 2014. Creating a SQL Server database for CONTROL will require access to SQL Server Management Studio for SQL Server 2005 and newer versions, as well as a database account that has permission to create SQL Server databases and SQL Server database accounts.
Connecting to SQL Server
The user should launch the Microsoft SQL Server Management Studio application in the normal fashion. Once the Microsoft SQL Server Management Studio is running the user should connect to the SQL database where the new database will be created. Under this server there should be several sub trees including Databases, Security, Server Objects, Replication, Management
Create the Database
To create the new database, in the Object Explorer select the Databases tree.
Right click on this selection and select New Database... This will bring up the New Database dialog
In the Database name field enter the name of the new database, in this example we will call our database Control. The name of the CONTROL database should have no spaces. The Database files information will automatically get filled based on the database name that you select. You can leave all the configuration options with their default values unless you specifically need to change something. Click the OK button to have SQL Server create the database.
Create the SQL Server CNTADM account
Now that we have created the database for CONTROL, the next step is to create the SQL Server account for CONTROL to use. CONTROL requires a single user account be created in SQL Server. This user account will be the SQL Server account that owns all the CONTROL objects in this database. The SQL Server user account for CONTROL is required to have the ID of “CNTADM”.
Before adding any SQL Server database users, the authentication method of the SQL Server needs to be set to allow both Windows authentication and SQL Server authentication. This property is set in the “Server authentication” section on the Security tab of the SQL Server’s Properties dialog.
To add the new SQL Server for CONTROL, in the Object Explorer select the tree named Security and then the sub tree named Logins
Right click on this selection and select New Login... This will bring up the SQL Server Login Properties dialog
1. In the Login name field, enter CNTADM. This account needs to be entered in upper case.
2. Select the “SQL Server authentication” option, and enter and confirm the password of CNTADM. This password needs to be entered in upper case. The password for the CNTADM database account can be changed from the default value of CNTADM after CONTROL has been setup
3. Un-check the “Enforce password policy” checkbox
4. For the Default database option, select the Control database that was created in the previous step
5. If your servers default language is “English” then you can leave the Default language selection setting as <default>. However, if your servers default language is not English, then you need to select “English” as the Default language for CNTADM
6. Select the User Mapping page from the “Select a page” control. On this page, check the checkbox in the “Map” column next to the Control database. The CNTADM user ID will appear in the “User” column for that row. For now leave the entry in the “Default Schema” field for this row, at its default value.
7. Click on the OK button to add the new user
After creating the CNTADM account it is required that you grant the CNTADM account the VIEW SERVER STATE permission. To perform this operation, you will need to open a new query window. You can open a query window from File menu by picking “New” and then “Query with Current Connection”. In the query window enter and execute the following SQL statement
USE MASTER;
GO
GRANT VIEW SERVER STATE TO CNTADM;
GO
You will get a message saying that the command completed successfully. You can close the query window.
Create the CNTADM Schema
The remaining steps are not required if the CONTROL database will be initialized by restoring from a SQL Server backup. If this is the case you can stop now and follow the directions on how to restore a CONTROL SQL Server database.
The next step is to create the CNTADM schema that will store CONTROL’s database objects. To create the new schema, in the Object Explorer expand the Database tree node so that all the databases are listed. Select the newly created database named Control, and right click and select the New Query option. This will bring up a query window for this database. In the query window enter and execute the following SQL statement (including case)
CREATE SCHEMA CNTADM AUTHORIZATION CNTADM
This will create the CNTADM schema in the Control database. You should see a message that the command completed successfully in the message window below the query. You can close the query window
Associate the CNTADM Schema
The next step is to associate the newly created CNTADM schema with the CNTADM user in the Control database. To associate the CNTADM schema, in the Object Explorer select and expand the Security tree and then expand the Logins sub tree and select the CNTADM login. Right click on the CNTADM login and select “Properties” to edit the CNTADM user. This will bring up the Login Properties dialog for the CNTADM login. Select the User Mapping page from the “Select a page” control. On this page, select the row for the Control database. The current Default Schema will be dbo.
In the “Default Schema” field for the Control database, enter “CNTADM” as the new default schema.
Click on the OK button to save the changes to the CNTADM login
Grant Database Permissions
The final setup step is to establish the required user permissions for the CNTADM account in the Control database. This is a one-time task that only needs to be only when a new database is created.
To grant the required user permissions for the CNTADM account in the Object Explorer expand the Database tree node so that all the databases are listed. Select the newly created database named Control, and right click and select Properties. This will bring up the Database properties - Control dialog. Select the Permissions page from the “Select a page” control. This page allows us to manage this database’s permissions.
In the Users or roles section, the user CNTADM should be listed. Select the CNTADM user ID from this list. This will allow you to configure the permissions for the CNTADM user in the Effective permissions table. In the Effective permissions table, check the “Grant” checkbox for the following permissions:
- Connect
- Create Default
- Create Function
- Create Procedure
- Create Rule
- Create Table
- Create View
- Delete
- Execute
- Insert
- Select
- Update
- View database state
Click the OK button to close the Database properties - Control dialog.
CONTROL Database Initialization/Restore
The new CONTROL database can now be initialized in two ways. One way is to restore a backup of an existing CONTROL database into the newly created database. This allows you to bring in a pre-built application. The other way is to initialize the newly created database with the CONTROL database Initialization program. This step is done from a client workstation that has been setup to run CONTROL. This process initializes the new database with just the basic required CONTROL objects. After running the CONTROL database Initialization program, CONTROL is ready to be used to develop the desired application. The instructions on how to run the CONTROL database Initialization program are described in the CONTROL Setup Guide document.