How to create a User and SQL server authentication

SQL Server Authentication is a default mechanism to connect to MS SQL Server, but there are times that you need to use SQL Server Authentication to connect to the server, like when you have to connect to an MS SQL Server Database on a different network or when you have an SSPI problem. In order to use SQL Server Authentication, you need to create a SQL Server Authentication Login ID first.

we will create a new SQL Server account.

Run Microsoft SQL Server Management Studio.

with local windows Authentication 


Expand the Security folder, and right-click on the logins folder, and then click New Login.


  1. Enter a login name and choose an authentication method.
  2. use SQL Server authentication and enter a password (and confirm it).



Click the Server Roles page and check the SysAdmin Role, or a the combination of DbCreatorDiskAdminProcessAdmin, and SecurityAdmin Roles.


Go to the User Mapping page and choose


Go to the Status page and set Permission to connect to the database engine to Grant and Login to Enabled.

then Click Ok

Server Authentication Mode

In the Object Explorer, right-click on the server node and select Properties.


Click on Security in the left menu.

Server authentication heading, select SQL Server and Windows Authentication mode.

Click OK.

In the Object Explorer, right-click on the server and select Restart. Once the server has been restarted.

You should be able to log in using SQL Server Authentication.


Related posts