CMS stores all site content and objects in a dedicated SQL Server 2000 database. Our first task is to create an empty database and configure the CMS system account permissions for this database.
Creating an Empty Database
To create a database, start SQL Server Enterprise Manager (Start > Programs > Microsoft SQL Server > Enterprise Manager); expand the Microsoft SQL Servers node and navigate to the server where you'd like the database to be created; right-click Databases and select New Database (Figure 8-1). In the Database Properties dialog box, type the name for your new database for example, MCMS (Figure 8-2). You can use any alphanumeric characters in the database name; just make sure that they are not all numbers. You cannot use any special characters, with the exception of the underscore character. After you've typed the database name, click OK. The empty database is created; it is shown in the Enterprise Manager under the Databases node.
Figure 8-1. Databases pane
Figure 8-2. Database Properties dialog box
CMS System Account Permissions on the Database
The CMS server performs database transactions at runtime as well as at design time. The CMS server runs under the CMS system account that is specified during the CMS installation. It is recommended that you use Windows authentication on the SQL server that hosts the CMS database. In order to be able to read and write to the database, the CMS system account must have db_datareader and db_datawriter permissions on the database. If you need to import data for deployment, then the CMS system account also requires db_ddladmin rights.
NOTE: When a new CMS site is configured, the system account does not require db_ddladmin rights on the CMS database. However, to avoid an error message in DCA later on, you can assign db_ddladmin rights on the CMS database to the system account. You can remove db_ddladmin rights after the CMS site has been created.
If SQL Server authentication is used, then the specified SQL Server account is used for all CMS database operations. This account needs appropriate permissions on the CMS database, as follows: db_datareader, db_datawriter, and db_ddladmin. The db_ddladmin permission is only required for importing data.
To grant permissions on the CMS database to the system account, in SQL Server Enterprise Manager, navigate to the server where you've created the CMS database. Expand the Security node and click Logins. In the Logins pane, select the CMS system account, right-click the account, and select Properties (Figure 8-3).
Figure 8-3. Logins pane
In the SQL Server Login Properties dialog box, select the Database Access tab; in the Permit pane select the CMS database by clicking the check box in the Permit column. After you've selected the database, the CMS system account name appears in the User column, and a list of roles for this user appears in the Permit in Database Role pane. In the Permit in Database Role list, scroll down and select db_datareader, db_datawriter, and db_ddladmin if required, as shown in Figure 8-4; then click OK.
Figure 8-4. CMS system account rights
We have created and configured the database to be used for CMS. You can now exit SQL Server Enterprise Manager.