Follow these directions to create a new user username and password in Microsoft SQL Server.
- Sign into Microsoft SQL Server as admin with Windows Authentication.
- In Object Explorer, expand the folder of the server instance in which you want to create the new login.
- Right-click the Security folder, select New > Login….
- In the Login – New pop-up box, enter the username for the new user.
- Bullet SQL Server authentication
- Enter the password twice
- Click OK.
To allow connections to SQL Server using SQL Server Authentication, we need to configure SQL Server to accept both SQL Server and Windows authentication.
- In Object Explorer, right-click on the root folder and select Properties.
- In the Server Properties pop-up box, select the Security page.
- Bullet SQL Server and Windows Authentication mode.
- Click OK.
- In Object Explorer, right-click on the root folder and select Restart.
By default, a newly created user will not have access to the databases in SQL Server. You will need to select which databases the newly created user will be able to access.
- In Object Explorer, expand Security > Logins.
- Right-click on the newly created user account and select Properites.
- In the Login Properties pop-up box, select User Mapping.
- Check-mark the databases you want the newly created user to have access to.
- Check-mark db_owner.
To ensure you are able to sign in with the newly created username and password, exit SQL Server Management Studio, and then launch SQL Server Management Studio again. Change Authentcation to SQL Server Authentication and use the newly created credentials. Also, in Object Explorer try to access the tables in the database you should have access to.