Logins and Users in SQL Server

In my previous article we've learned about the authentication modes available in SQL Server. Here's the link:

• Authentication Modes Available in SQL Server

So let's get started.

A login in SQL Server is something that is used for authentication. A login is a security entity that can be authenticated by SQL Server or any Secure System. When we create a user, he needs a login to connect to SQL Server. This login is required when you connect to SQL Server via "SQL Server Authentication" mode.

The process of verifying any specific login by SQL Server or any system is called authentication.

In SQL Server a Login and a User are different from each other, they're not the same. A Login in SQL Server is a server-level security principal whereas a User in SQL Server is a database-level security principal.

Logins: Server Level
Users: Database Level

A login in SQL Server can be mapped to multiple databases but users can only be mapped as one user in each database.

Login details are stored in the master database whereas User details are stored in the database where it's mapped.

The scope of the login is one entire server and the scope of a user is one of the mapped databases. If the user wants to connect to the instance of SQL Server, a login must be mapped to the user.

We can grant and deny permission inside a database to the user not to the login.

Logins are stored under sys.syslogins whereas users are stored under sys.sysusers of the mapped database as shown below.



Note that both of the tables from both of the databases are linked. Both of the tables have the same column named SID as in the following screenshot:



I hope until here you've understood the difference between logins and users. Now let's create a new login.

To create a new login, we've two ways, they are:

• SSMS GUI
• T-SQL

First we'll see from SSMS HUI, then we'll go through T-SQL.

To create a new Login from GUI, go to Object Explorer, Expand folders of Server and Expand Security Folder. Right-click on Logins as in the following .



This will open new Login Window as in the following:



Here, you've to enter your login Name and types of authentication. If you select authentication type as "Windows authentication", SQL Server will use Windows login that means you don't need to create additional password or any username. If you use "SQL Server Authentication", your login name will be your username and you've set your password in password field as shown below.



When you select "SQL Server authentication" as authentication mode, you'll see SQL Server enables 3 options with checkboxes as shown below.

• Password Policy.
• Password Expiration.
• Change Password at next login.

So, what are these options???

When we select second type of authentication, SQL Server uses Windows password policies when creating new user credentials.

When you check "Enforce password policy" it will follow windows policy.

When you check "Enforce password expiration", SQL Server reminds user to change old passwords and accounts that have expired passwords are disabled.

3rd option will prompt user to change default password when user login for the first time with the credentials in SQL Server.

Select the default database for new login.



I'm selecting master database. This is because when user opens new query window, connection will open with databases selected here.

Now we'll map database to this user. To do this select User Mapping and check the database you want to map to User as shown below.



You can also give type of role to the login for the database while creation, SQL Server gives public role by default as shown below.



Now click on the Status option and ensure Login is enabled as shown below:



Now click on the OK button and your login will be created.

To create a login and user using T-SQL, the following is the code:

USE [master]
GO
CREATE LOGIN [CSharpCorner] WITH PASSWORD=N'cs@1234' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
USE [CSharpCorner]
GO
CREATE USER [CSharpCorner] FOR LOGIN [CSharpCorner]
GO

We're done with all the process and created a login and user in SQL Server. To verify your login and user expand the Login folder and you'll see your created login as shown below.



To see your created user, expand the database folder, then expand your database, security and users. Here you'll see your created user as shown below.



We're done with our article on creation of login and user.

In this article we've learned what is a login and user are, we've also learned the differences between them. Then we've created new logins and new users using GUI and T-SQL. I hope you now understand the concept of logins and creation of logins in SQL Server.

In our next article we'll explore some other concepts related to SQL Server.

Please provide your valuable feedback and comments that enable me to provide a better article the next time.