How to solve the problem of invalid isolated users and object names 'xxx' caused by SQL Server database backup and Restoration

How to solve the problem of invalid isolated users and object names 'xxx' caused by SQL Server database backup and Restoration Time : 2006-11-24 11:36:08 Source : Csdn Author : In the process of using the database, zhiguang often encounters 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.

I. Isolated 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, there is no corresponding Login User Name. Even if you have created a test login user name and a previous user password, 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. First, let's talk about the first method. 1. Modify the object owner 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 test user logs on to as testdb, you can use test to log on to the user and then operate the database. The stored procedure 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' will be 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 or a Windows group that does not have a corresponding database-level subject, a database user is created. Example: Change the owner of the authors table to DBO. Exec sp_changeobjectowner 'authors ', 'dbo'; the biggest drawback of the Go method is that when there are many tables, in addition, modifications such as stored procedures or triggers are very troublesome and need to be made one by one. Therefore, they are not recommended. 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. 2. Match the login user with the isolated database user. In fact, after we create a database login user with the same name, we still cannot use the tables in the database because of the different Sid values, that is, the username in the system login table is the same as the username in the Database User table. It is the SID field in the database or the SID value of the old system. Therefore, we need to convert it to the one we created, databases rely on 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', and 'test'. The system will prompt that an isolated user is fixed. If you have not created a logon user for test, you can use sp_change_users_login 'Auto _ fix', 'test', null, 'testpassword' to create a logon user named test, the user whose password is testpassword corresponds to it. 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. Invalid object name 'xxxx' someone may encounter an 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.