Create and manage a database with multiple schema on SQL server
Creating a demo database
Follow the following step to create a database on-premises:
- Download SQL server on your local machine. https://www.microsoft.com/en-us/sql-server/sql-server-downloads
- 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
- 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