Database objects for Microsoft SQL Server

About this task

In these steps you create a user to be the database owner and the database for Build Forge to use. If necessary, you can also create a schema for Build Forge to use.

Procedure

Create a user to serve as the database owner. Build Forge uses this username to log on to the database. Use build unless you must use a different name. The user must have full permissions.
1. Open SQL Server Management Studio.
2. Open the database server in the Object Explorer (left panel).
3. Right-click the Security folder and choose New > Login.
4. In the Login - New dialog, specify the login name and choose options as follows. Important: uncheck User must change password at next login.
  - Choose SQL Server authentication and provide a password.
  - Uncheck Enforce password expiration
  - Uncheck User must change password at next login

Create the database. You must use mixed-mode authentication. The following interactive and script examples assume that you will use the default schema of DBO.

Open SQL Server Management Studio.
Open the database server in the Object Explorer (left panel).
Right-click the Databases folder and chose New Database.
In the New Database dialog, specify parameters for the database:
- Specify a Database name. Use build unless you must use another name. The name is case-sensitive. The name of the data and log files are updated automatically in the Database files box.
- Specify the Database owner.
  - Click the [..] control to the right of the field.
  - In the Select Database Owner dialog, click Browse.
  - Check the name of the user you created, then click OK.
  - Click OK in the Select Database Owner dialog.
- Specify the Database files parameters. In the Database files table, do the following:
  - For both files: set the Initial Size to 500 (in MB)
  - For both files: set Autogrowth. In the Autogrowth column, click the [..] control to open the dialog. Check the Enable Autogrowth box, set growth to 500 MB, and select Unrestricted Growth, then click OK.

Alternatively, you can use the following script to create the database.

CREATE DATABASE [build] ON  PRIMARY 
( NAME = N'build', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\build.mdf' , 
    SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'build_log', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\build_log.ldf' , 
    SIZE = 1024KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'build', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [build].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [build] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [build] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [build] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [build] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [build] SET ARITHABORT OFF 
GO
ALTER DATABASE [build] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [build] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [build] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [build] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [build] SET CURSOR_CLOSE_ON_COMMIT ON 
GO
ALTER DATABASE [build] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [build] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [build] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [build] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [build] SET READ_COMMITTED_SNAPSHOT ON
GO
ALTER DATABASE [build] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [build] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [build] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [build] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [build] SET  READ_WRITE 
GO
ALTER DATABASE [build] SET RECOVERY FULL 
GO
ALTER DATABASE [build] SET  MULTI_USER 
GO
ALTER DATABASE [build] SET PAGE_VERIFY CHECKSUM  
GO
USE [build]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') \
     ALTER DATABASE [build] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

Note: The READ_COMMITTED_SNAPSHOT attribute must be set to ON.

To test for READ_COMMITTED_SNAPSHOT, run the following SQL statement.

SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name='build'

Substitute your database name if you did not use build. The statement must return "1".

If the statement returns "0", run the following SQL statement.

ALTER DATABASE [build] SET READ_COMMITTED_SNAPSHOT ON

Set the default database for the user.
1. Open SQL Server Management Studio.
2. Open the database server in the Object Explorer (left panel).
3. In Object Explorer, open Security > Logins.
4. Right-click the user you created and choose Properties.
5. On the General page, select a Default database. Select the database you created.
6. Click OK.
Create a schema, if needed. You do not need to create a schema if you can use the default schema DBO. Using the default is recommended. If you need to use a different schema, create it in the Build Forge database and assign the owner to be the database user you created. The following example creates schema bf_schema and sets build as the owner:
```
USE build;
GO
CREATE SCHEMA bf_schema AUTHORIZATION build;
GO
```

Feedback