SQL Server 2016: Create a Login
In SQL Server, you can create a login to allow different users or processes to connect to the SQL Server instance.
As with any client/server database management system, a given instance of SQL Server will typically be accessed by many different users and processes. These could include database developers, database administrators, web applications, corporate CRMs, reporting applications, etc.
Each of these users need a login to connect to SQL Server. A login can be used by one, or a group of users.
When you create a login, you can choose whether it uses Windows Authentication or SQL Server Authentication login.
- Windows Authentication
- When using Windows authentication, SQL Server validates the account name and password using the Windows principal token in the operating system. Therefore the user's identity is confirmed by Windows. SQL Server does not ask for the password, and does not perform the identity validation.
- SQL Server Authentication
- When using SQL Server Authentication, logins are created by using SQL Server and they are stored in SQL Server. Users connecting using SQL Server Authentication must provide their login credentials every time they connect.
You can also apply other settings for the login, including adding it to one or more roles, mapping it to database users, and more.
Launch the New Login Dialog Box
Configure the new Login
Test the new Login
Now, let's test out the new login by using it to connect to SQL Server.
Open the Connection Dialog Box
Connect using the New Login
Access the Database Objects
Server Authentication Mode
Before a user can log in using SQL Server Authentication, the server must support this mode.
If you receive a 18456 authentication error when trying to log in, check the server's authentication mode. You need to ensure it is configured to support SQL Server Authentication.
Here's how to change the authentication mode in SQL Server.
Launch the Server Properties Dialog Box
Change the Authentication Mode
Restart the Server
Logins vs Users
Logins and users are two different things in SQL Server.
- A login grants the principal access to the server.
- A user grants a login access into a particular database.
So the above login is allowed to connect to SQL Server. However, at this stage, none of the databases on the server have a user associated with this login.
We'll do that next. We'll create a database user, and associate it with the above login.