Cause:
This is what we usually call "isolated users". The so-called isolated account means that a database account only has a user name but no logon name. Such a user exists in the sysusers system table of the user Library, but there is no corresponding record in syslogins of the master database.
Two types of isolated accounts are generally generated:
1. Restore the backed up database on another machine;
2. After the system or SQL SERVER is reinstalled, only the user Library is restored.
Solution:
Delete xxx from database sysusers
Correspond login users to isolated database users
In fact, after we have created a database login user with the same name, we still cannot use the tables in the database because of the different sid, that is, the user names in the system login table are the same as those in the database user table, only the sid Field is used. The sid value of the old system is used in the database. Therefore, we need to map it to the new one. The database uses the sid to identify users.
The stored procedure sp_change_users_login can be used here. It has three actions: report, update_one, and auto_fix.
Run sp_change_users_login 'report'. The system lists the number of isolated users of the current database.
We only need to select the current database as testdb and then run
Sp_change_users_login 'update _ one', 'test', 'test'
The system prompts you to fix an isolated user.
You can also use
Sp_change_users_login 'auto _ fix', 'test', NULL, 'testpassword'
Create a user with the logon username "test" and the password "testpassword.
Now, the problem of accessing database objects has been solved. If multiple databases have data tables of the same user, you only need to select different databases and execute the update_one operation.
1. View isolated users of a database:
USE database name
EXEC sp_change_users_login 'report'
2. Automatic repair of an isolated user:
USE database name
EXEC sp_change_users_login 'auto _ fix', 'isolated username', NULL, 'password'
The password indicates that when the user's logon does not exist, the system automatically creates a logon and assigns a logon password.
Error:
Server: message 8144, level 16, status 2, process sp_change_users_login, row 0
Too many parameters are specified for the process or function sp_change_users_login.
When an error is reported, you can delete the ", 'password'" section (version or patch issues)