Shulou(Shulou.com)06/01 Report--

This article mainly introduces the SQL server prompt object name invalid how to solve the relevant knowledge, the content is detailed and easy to understand, the operation is simple and fast, has a certain reference value, I believe that after reading this SQL server prompt object name invalid how to solve the article will have something to gain, let's take a look.

The problem of invalid SQL object name is mostly caused by data migration, so let's give a solution. In the process of using the database, we often encounter the problem of database migration or data migration, or there is a sudden database corruption, which needs to be recovered directly from the backup of the database. However, problems arise at this time, and here are several solutions to common problems.

First, the problem of isolating users, for example, many tables in the previous database were established by the user test, but when we restore the database, test users become isolated users at this time, without a corresponding login user name, even if you have established a test login user name, and it is the previous user password, after logging in with this user, there is also no way to operate the user table that used to belong to test.

There are two ways to solve this problem. Let's first talk about the prerequisites for the solution. First of all, we need to restore the database with backup files. Our database here is called testdb, and there are user tables belonging to user test. This is easy to operate, not to say much, in the enterprise manager is very convenient to restore. After the recovery, an orphaned user test is generated. Then, log in with a sa user or a user with DBA privileges to create a test database login user with a password that can be set at will or consistent with the previous one. We use it to correspond to isolated test users.

Make the login users correspond to the isolated users of the database. In fact, after we have established a database login user with the same name, we still cannot use the tables in the database. Because of the difference in sid, the user name in the system login table is the same as that in the database user table. Only in the sid field, the Sid value in the database is still the same as that of the old system, so we have to correspond it to our new. The database relies 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.

Running the sp_change_users_login 'report', system lists the number of orphaned users in the current database.

All we have to do is select the current database as testdb, then run sp_change_users_login 'update_one','test','test'-- and we'll be prompted to fix an orphaned user.

If you do not have a login user for test, you can also use sp_change_users_login 'Auto_Fix',' test', NULL, 'testpassword'-- to create a corresponding user with a login user name of test and password of testpassword. Well, usually at this point, the problem of database object access has been solved. If you have multiple databases with the same user's data table, you just need to select a different database and execute the update_one one.

2. As soon as you open SQL server Manager Studio and want to see the information in the table, write a query statement. As a result, there is a red line under the table name, and the execution indicates that the object name is invalid:

This is because the database name is not selected, because test is just a table name, and the database defaults to the master database:

At this point, select the database as the database where the table is located in the drop-down list.

Third, the cache problem is relatively simple, but it should not happen when the database is first opened, but it should happen after several operations have been done. The situation is the same as the first one. The solution is to clear the cache and ctrl+shift+r the shortcut key.

This is the end of the article on "how to solve the problem of invalid object names in SQL server". Thank you for reading! I believe that everyone has a certain understanding of the knowledge of "how to solve the invalid object name in SQL server". If you still want to learn more knowledge, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.