Document the steps we took to setup the academies 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
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.
All of the following steps must be run in the context of the database itself i.e. sip
USE [sip];
The schema isolates the Rails application data within the database, create the schema:
CREATE SCHEMA complete;
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.
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;
Now we have the user and schema, we grant the permissions to the user on the schema:
GRANT CONTROL ON SCHEMA::complete TO complete;