Post: ms SQL Server 2000 Solution to the Problem of invalid isolated users and object names 'xxx' caused by database backup and Restoration

When using a database, you often encounter database migration or data migration problems, or sudden database damage. In this case, you need to directly recover from the database backup. However, a problem may occur at this time. Here are several solutions to common problems.

1. Isolate users

For example, many tables in the previous database were created by user test. However, after we restored the database, the user test became an isolated user and did not have the corresponding login username, even if you have created a user name for test logon and the password of the previous user, you cannot operate the user table that previously belonged to test after logging on to the user.

There are two solutions to this problem.

The prerequisites for the solution are described first.

First, we need to use the backup file to restore the database. The database name here is testdb, which contains the user table test. This is easy to operate and can be easily restored in the Enterprise Manager. After recovery, an isolated user test is generated.

Then, log on with the SA user or a user with DBA permissions, and create a test database login user. The password can be set at will and can be consistent with the previous one. We use it to correspond to isolated test users.

The following two methods are available: the first is to change the owner of the object, and the second is to match the login user and the isolated database user.
Method 1:Modify Object owner

It is to change the user table that originally belongs to the database user test to the user table that belongs to DBO, and then modify the database that the user logs on to the test database as testdb, you can use test to log on to the user and then operate the database.

Stored Procedure used

Sp_changeobjectowner [@ objname =] 'object', [@ newowner =] 'owner'

 

Parameter description:

[@ Objname =] 'object'

The name of a table, view, user-defined function, or stored procedure in the current database. The object is nvarchar (776) and has no default value. If the schema and its owner have the same name, the object can be limited by the existing object owner, in the format of existing_owner.object.

[@ Newowner =] 'owner'

The name of the security account of the new owner of the object. The owner's data type is sysname, and there is no default value. The owner must be a valid database user, server role, Microsoft Windows logon name, or Windows group that can access the current database. If the new owner is a Windows user without a corresponding database-level subject or
For a Windows Group, a database user is created.

Instance:

The following example changes the owner of the authors table

DBO.

Exec sp_changeobjectowner 'authors ', 'dbo ';

Go

The biggest drawback of this method is that when there are a large number of tables, and there are stored procedures or triggers to modify it, it will be very troublesome and need to be modified one by one. Therefore, it is not recommended to use this method. Now there is a batch-modified storage process on the Internet. You can find it and leave it blank. Instead, we will teach you a simpler one.

Method 2: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 the following code to list the number of isolated users of the current database.

Sp_change_users_login 'report'

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.

Ii. Object NameInvalid 'xxxx' 

Some people may encounter Invalid object name 'xxxx. The system table does not cause this problem, but the user table must be added with the user name, and then indicates that access is allowed. For example, select * from author indicates that the object name author is invalid, select * from test. author can be accessed. This is the user's preferred identity.

The solution is simple: Check whether the user who logs on to the test has the DBA permission or the system management permission. If yes, remove it.

If the user has the DBA identity, the default tablespace after login is the system tablespace of DBO, so after removing it, it will access the data table with the normal test tablespace.