What's the Difference between SQL Server Login and Database User

By Hally | Last Updated January 04, 2024

Part 1: Difference between SQL Server login and Database user

1. If the SQL Server login is the Windows system account, SQL login is specific for database server, and SQL Server database user is set for specific SQL Server Database.

2. If SQL Server login account is the login user for SQL Server database, it is different from Database user. If you have several databases, every database user has its login user for SQL Server. And at the same time, one or few login users only belong to a specific database user.

So far, we should know that SQL Server login is associated with database user, and they are different at the same time.

SQL Server login is used to connect to SQL Server, and database user is used to access specified database. Only with SQL Server login and database user at the same time, could we login into SQL Server and access specific the database.

So, how to create login name and database user for SQL Server?

Part 2: Difference between creating SQL Server login and Database user

From the detailed introduction about them, we see that maybe steps to create SQL Server login are similar to create database user. And they seem to have so much in common. So when we create them, we probably have a wrong feeling that we are creating the same. Actually, they are not the same. Therefore, the following passage shows special and important differences during creating SQL Server login and database user.

1. Preparation steps of creation:    

1-1 Create SQL Server login:

• In Object Explorer, expand the folder of the server instance in which you want to create new login.
• Right-click the Security folder, point to New, and select Login.

1-2 Create Database user:  

• In Object Explorer, expand the Databases folder.
• Expand the database in which you want to create the new database user.
• Right-click the Security folder, point to New, and select User.

2. Steps to make special settings during creation:

2-1 Create SQL Server login:

In the Login – New dialog box, on the General page, enter the name of a user in the Login name box. Alternately, click Search to open the Select User or Group dialog box.

2-2 Create Database user:

In the Database User – New dialog box, on the General page, select user types and enter a name for the new user in the User name box. And then in the Login name box, enter the login for the user.

These are main differences between creating SQL Server login and Database user.

After above introductions, maybe you still have several questions about creating login and database user. So go on to read this passage.

For example:

Q1: Could I use "sa" login for all of database?

Of course, you could. But "sa" permission is so high that it would be dangerous for database when we use it to login SQL Server. Hence we can create another login user and grant it appropriate permission for SQL Server database.

Q2: How to solve this problem, there is only name for SQL database user, but no login for it?

If the database has been just created, then we should create new login for SQL Server, and make database user mapped to login user.

If the SQL Server login lost is because of SQL database backup and restore, and database user becomes isolated user, we couldn't use it and delete it.

What we should do are:

Step 1: Check whether there is only name for database user and no login name for it.

Step 2: Login query analyzer with Sa account and run the query:

Use 'restored database name'
Exec sp_addlogin 'new login name'                        
Go
Exec sp_change_users_login 'UPDATE_ONE', 'database user without login name', 'new login name'
Go

Step 3: In Object Explorer, click Security and expand Logins, right-click login name and choose Properties. Change login password in Login Properties dialog box.

Related Articles:

• 4 Effective Ways to Change SQL Server SA Password
• How to Reset SA and Other User Passwords for SQL Server
• Why Cannot Login into SQL Server with Windows Authentication
• How to Login SQL Server after SA Account Locked Out
• How to Set Server Access with Single User Mode