Creating a demo database

Follow the following step to create a database on-premises:

1. Download SQL server on your local machine. https://www.microsoft.com/en-us/sql-server/sql-server-downloads
2. After installation, connect to it using SQL Server Management Studo. To download SQL Server Management Studio, kindly go to this link: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15
3. Now you have connection to database, the default schema is "dbo", if you don't specify any schema, you will automatically create for example a table under "dbo" schema.

The goal is we plan to create a schema for each client and only that client can access it's own schema, not any clients else. Under each schema, there should be exactly the same tables as what we have currently on cloud.

• Create authentication users:

In the Database User - New dialog box, on the General page, select one of the following user types from the User type list:

• SQL user with login
• SQL user with password
• SQL user without login
• User mapped to a certificate
• User mapped to an asymmetric key
• Windows user

For example to create a user with login and password:

– Creates the login AbolrousHazem with password '340$Uuxwp7Mcxo7Khy'.

CREATE LOGIN AbolrousHazem

WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';

GO

– Creates a database user for the login created above.

CREATE USER AbolrousHazem FOR LOGIN AbolrousHazem;

GO

Reference Link: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-user?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-ver15

• Create schema under certain users and grant access:

CREATE SCHEMA Sprockets AUTHORIZATION Annik

CREATE TABLE NineProngs (source int, cost int, partnumber int)

GRANT SELECT ON SCHEMA::Sprockets TO Mandar

DENY SELECT ON SCHEMA::Sprockets TO Prasanna;

GO

• Create tables under certain schema:

CREATE TABLE client2.peronalInfo (

Name NCHAR (10) ,

Age NUMERIC (18,0) ,

Gender NCHAR(10),

);

Go

CREATE TABLE client2.temprature (

temprature VARCHAR (50),

personalid INT,

);

CREATE TABLE client2.infos (

first_name VARCHAR (50) NOT NULL,

last_name VARCHAR (50) NOT NULL,

phone VARCHAR(20),

);