Settings on SQL Server Management Studio and SQL Server Configuration Manager

Prerequisites

Procedure

  1. Open SSMS (SQL Server Management Studio) and under Object Explorer, expand Security

  2. Right click on Logins, create a New Login and enter a username and a password

  3. Beneath General, located in the left bar, select Server Roles and enable public

  4. Create a new database by right-clicking on Databases. Name it planet9 (keep all options on default)

  5. Create a new user under planet9 from Database > Security > Users and give the user that was created in the above steps:

    ms sql database user
  6. Under Owned Schemas, set the following parameters:

    ms sql schemas
  7. Under Membership, set the following:

    ms sql membership
  8. Create a new Schema under the planet9 database and name it planet9:

    ms sql new schema
  9. Search for Schema owner. In the below picture we are adding the same user that we created under Databases:

    ms sql new schema
  10. Set the Server Authentication :

    ms sql server authentication first
    ms sql server authentication second
  11. Restart the server

Last steps:

You can now, test the connection to the SQL Server with the demosql user.

  1. Open the SQl Server Configuration Manager and enable TCP/IP for the SQL Server by right-clicking on TCP/IP

  2. After the previous step, go back to SSMS and restart the server

Results

  • Provided that all the information is accurate, you shall be duly connected to the SQL Server utilizing the demosql user