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?
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-1 Create SQL Server login:
1-2 Create Database user:
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.
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.
Password Recovery Software