Error resolution for SQL Server database using Backup to restore orphaned users and object name ' xxx '

server| Backup | error | object | solve | data | database

Describes an error resolution for SQL Server databases that cause orphaned users and object names ' xxx ' with a backup restore

In the process of using a database, you often experience the problem of database migration or data migration, or if there is a sudden database corruption, you need to recover directly from the backup of the database. However, there are problems, and here are some solutions to common problems.
I. The problem of orphaned users
For example, many of the tables in the previous database were created by user test, but when we recovered the database, the test user became an orphaned user with no login username, even though you created a test login username and a previous user password. The user who has previously belonged to test is also not able to manipulate the user's table after logging in.
There are two ways to solve this problem.
First, the prerequisite for the solution.
First, to restore the database with the backup file, our database here is named TestDB, which has a user table that belongs to user test. This is very easy to operate, not much to say, in Enterprise Manager is very convenient to restore. After the recovery, the orphaned user test is generated.
Then, log in with the SA user or a user with DBA authority, create a test database login user, set the password casually, and be consistent with the previous one. We use it to correspond to the isolated test user.
There are two ways to do this, the first is to change the owner of the object, the second is to make the logged-in user and the database of orphaned users. First of all, let's start with a method.
1, modify the object is the main
The user table that originally belonged to the database user test was modified to a user table belonging to the DBO, and then the test login user's database was modified to TestDB, and the database could be manipulated directly using test login.
Stored procedures to use
sp_changeobjectowner [@objname =] ' object ', [@newowner =] ' owner '
Parameter description
[@objname =] ' Object '
The name of an existing table, view, user-defined function, or stored procedure in the current database. object is nvarchar (776) and has no default value. If the schema and its owner have the same name, object can be qualified by the existing object owner, in the form of Existing_owner.object.
[@newowner =] ' Owner '
The name of the security account that will be the new owner of the object. Owner's data type is sysname, and there is no default value. 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 principal, the database user is created.
Instance:
The following example changes the owner of the authors table to dbo.
EXEC sp_changeobjectowner ' authors ', ' dbo ';
Go
One of the biggest drawbacks of this approach is that when the number of tables is very large, and there are stored procedures or triggers such as modifications can be very cumbersome, need a change, so do not advocate the use. Now there is a batch modification of the online stored procedures, we can find, here is not to say, but to teach you a simpler.
2, so that the logged-in user and the database of isolated users of the corresponding
In fact we have established the same name for the database login after the user, tables in the database we still can't use the same as the SID, which is the same as the user name in the system login table and database user table, the SID field, the SID value of the old system in the database, and so we're going to match it to our new Databases rely on SIDS to identify users.
Stored procedure sp_change_users_login can be used here. It has three kinds of movements, namely Report,update_one and Auto_Fix.
Running sp_change_users_login ' ", the system lists the number of orphaned users for the current database.
We just 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 orphaned user.
If you do not have a logon user to create test, you can also use the
Sp_change_users_login ' Auto_Fix ', ' Test ', NULL, ' Testpassword '
To create a login user named Test, which corresponds to the user with the password Testpassword.

Okay, here we go. Typically, database object access issues have been resolved. If there are multiple databases with the same user's datasheet, just select a different database and execute the Update_One.

Second, the object name ' XXXX ' invalid problem

There may be a problem with the invalid object name ' xxxx '. The system table does not create this problem, and the user table will be added with the username, and then it will be accessible, such as SELECT * from author, which says that the object name author is invalid and the select * from Test.author can be accessed, this is the user's preferred identity problem.

The solution is very simple, is to see whether the user logged in with DBA authority or system administration permissions, some words to remove the line.
Because if the user has DBA status, then the default tablespace is the dbo system tablespace, so when it is removed, the datasheet is accessed in the normal test table space.

Well, basically there are so many questions, if there are questions, welcome to my forum to discuss:
http://www.inspriesky.com/bbs/. I wish you all the best.