SQL Server Authentication

In the previous chapter, you learned about creating a login using Windows user account. Here, you will learn to create a login using SQL Server user.

At the time of installing SQL Server, if you select Mixed Mode, then you must provide a password for the built-in System Administrator or sa account. It is highly recommended to create a strong password for the sa account; otherwise disable this account as it is mapped to the sysadmin server role and has administrative rights on the whole server. Hence it is vulnerable to attack by hackers.

Create a New Login with SQL Server Authentication

The login created using SQL Server authentication mode is independent of Windows user accounts. Login and password are created and stored in the syslogins table in the master database. Passwords are not stored as plain text.

To create a new login using SQL Server authentication, follow the steps:

Open SQL Server Management Studio. In the object explorer, expand the Security node. and right-click on the Logins node and select New Login.