DfE Complete conversions, transfers and changes

academies_database_provisioning.md

Provision the Academies database for complete

Document the steps we took to setup the academies database.

Access to the database

The following will be required:

• SQL server admin (sa) login or equivilent persmissions
• The database itself to exist on the server, here the database name is sip

Create the login

The Rails application will login to the database using it's own user and credentials, create this login:

USE master;
CREATE LOGIN complete WITH PASSWORD = 'strongPassword&';

By default MSSQL will require the password to include upper and lowercase letters and a special character.

Switch to the database

All of the following steps must be run in the context of the database itself i.e. sip

USE [sip];

Create the schema

The schema isolates the Rails application data within the database, create the schema:

CREATE SCHEMA complete;

Create the user and assign the default schema

SQL server has separate login and users, we need both; This database user is assigned to the schema and only that schema: Create the user:

CREATE USER complete FOR LOGIN complete WITH DEFAULT_SCHEMA = complete;

Setting the default schema means the Rails application, via the user, will always write it's tables to the schema and nowhere else.

Allow the user to create new tables

The user needs to be able to crete new tables in the database as this cannot be granted to the schema alone.

GRANT CREATE TABLE ON DATABASE::[sip] TO complete;

Allow the user control in the schema

Now we have the user and schema, we grant the permissions to the user on the schema:

GRANT CONTROL ON SCHEMA::complete TO complete;