Creating the Database: without SQL Server Administrator Credentials

If you are upgrading an existing CAM database, you do not need SQL Server administrator credentials. In this case, you can follow the procedure outlined in Creating the Database: with SQL Server Administrator Credentials. You'll run dbinstall.exe with the /N option in place of the /A and /B SQL credentials options.

To create the CAM database when you don't have SQL Server administrator credentials, you need to work in tandem with someone who has those credentials as follows:

  1. The user with SQL Server administrator credentials needs to create the CAM database and user for you, following the steps described next. Note that the CAM database machine must have a supported version of SQL Server installed. See Product Requirements.
  2. Once the CAM database and user are created, you need to follow the second procedure below to initialize the database.

Manually Creating the CAM database and CAM database User

Creation of the CAM database and the CAM database user requires database server administrative privileges. You can use the EMSQL tool (installed in the folder where you installed the tools, typically C:\Program Files (x86)\Cherwell Asset Management\Database Tools), another database tool such as SQL Management Studio, or the OSQL command line tool to execute these SQL commands.

For complete command line OSQL syntax, bring up a DOS box and type OSQL.EXE -? to view the required syntax; each of the following steps require you to issue each specified SQL statement through OSQL.

All of the following SQL commands must be issued using the database administrator account (typically "sa") or with NT authentication under an account with database administrator permissions in SQL Server.

To create the CAM database and CAM database user with required rights to the database:
  1. Create the CAM database:
    CREATE DATABASE CAMDB
                ON (NAME='CAMDB_Data', FILENAME='E:\Databases\CAMDB_Data.mdf', SIZE=50MB)
                LOG ON (NAME='CAMDB_Log', FILENAME='E:\Databases\CAMDB_Log.ldf', SIZE=25MB)

    This command creates a database called "CAMDB" with data and log files located in the E:\Databases\ folder.

  2. Use the following command to select the CAM database:
    use CAMDB
  3. Use the following commands to set the current database to the newly-created CAM database and then create a database user named CAMUser with a password of CAMpwd:
    if not exists (select * from sys.syslogins where loginname = N'CAMUser') 
        CREATE LOGIN CAMUser WITH PASSWORD='CAMpwd', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
    if not exists (select * from sys.database_principals where name = 'CAMUser') create user CAMUser
    if not exists (select * from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME = 'CAMUser')
        exec sp_executesql N'CREATE SCHEMA CAMUser AUTHORIZATION CAMUser'
    ALTER USER CAMUser WITH DEFAULT_SCHEMA = CAMUser
  4. Give the CAM database user required rights to the CAM Database; for example, run:
    ALTER USER CAMUser WITH LOGIN = CAMUser
    exec sp_grantdbaccess N'CAMUser', N'CAMUser'
    exec sp_addrolemember N'db_owner', N'CAMUser'
            ALTER LOGIN CAMUser with DEFAULT_DATABASE = CAMDB
    exec sp_password N'CAMpwd', N'CAMpwd', N'CAMUser'

    These commands give the CAM database user db_owner the privileges required to execute the initialization process. (Note: These statements must also be executed with the CAM database as the current database.)

Once these commands are executed, the CAM database and CAM database user account are ready for initialization, which is described next.

Initializing the CAM database

After a user with SQL Server administrator credentials creates the CAM database and user, you need to initialize the database:

  1. From the machine where you installed the tools, bring up a DOS prompt from the folder where you installed the tools (typically, C:\Program Files (x86)\Cherwell Asset Management\Database Tools
  2. Run dbinstall.exe with the following parameters:
    /d <cam-database-name>/U <cam-database-user>/P <cam-database-user-pwd>/S <database-server-name[\instance]>/N
    The following example initializes the CAM database on the sql2014 instance of the SQL server named theserver using the specified SQL user and credentials (CAMUser, mycampwd):
    dbinstall.exe /d CAMDB /U CAMUser /P mycampwd /S theserver\sql2014 /N