Home > Resources > SQL Server > Difference between SQL Server Login and Database User

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

difference between SQL Server login and Database user

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:

create SQL Server login

1-2 Create Database user:  

create SQL Server Database user

2. Steps to make special settings during creation:

2-1 Create SQL Server login:

In the LoginNew 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 UserNew 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.

connect Database user to SQL Server login

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'                        
Exec sp_change_users_login 'UPDATE_ONE', 'database user without login name', 'new login name'

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.

change SQL Server login properties


Related Articles:

Hot Categories
Windows Password
Product Key Finder
SQL Password
More +
Hot Articles
4 Ways to Change SQL Server SA Password
SQL Server 2012 SA Password Reset
How to Login SQL Server after SA Account Locked Out
How to Change Server Authentication Mode in SQL Server
How to Enable Mixed Mode Authentication for SQL Server
More +
Latest Articles
How to Correct SQL Login Failed Error after SA Password Forgot
Solve SQL Server Connection Error 26
How to Fix Login Failed to SQL Server
More +
Hot Products
Windows Password Genius
iTunes Password Genius
Product Key Finder
More +