Introduction This article mainly introduces the solution to the prompt object name in SQL Server, and the presentation is very detailed in the text, with a certain reference value, interested partners can refer to it.
Most of the problems that generate SQL object names are due to data migration, which we will give a solution. During use of the database, you often encounter database migration or data migration, or have a sudden database damage, this You need to recover directly from the backup of the database. However, there will be problems at this time, and there will be a solution to several common problems.
I. Isolated users, for example, many tables in the previous database are built by the user TEST, but when we recover the database, Test users become an isolated user, there is no login user name, even if you have established it. A Test login username, and is the previous user password, and uses the user after logging in to operate the user table that is previously TEST.
There are two ways to solve this problem. Let’s talk about the prerequisites. First, use the backup file to restore the database, we have the database name here with TestDb, which is a user table belonging to the user Test. This is easy to do, not much, it is easy to recover in the Enterprise Manager. After recovery, Isolated User Test is generated. Then, log in with a SA user or a user with DBA privilege, create a Test database login user, the password is set casually, or it can be consistent with previous maintenance. We use it to correspond to the isolated TEST user.
Make the isolated user of the login user and the database, and after we establish the same name, after login users, tables in the database do not use because the SID is different because the SID is different, the system login table and the user name in the system login table and the database user table. Single is the SID field, the database in the database is the SID value of the old system, so we have to correspond to our newly built, the database relies on the SID to identify users. Here you can use the stored procedure sp_change_users_login. It has three action, named, update_one, and auto_fix.
Run sp_CHANGE_USERS_LOGIN ‘Report’, the system will list the number of isolated users of the current database.
We only need to select the current database as TestDB, then run sp_change_users_login ‘update_one’, ‘test’, ‘test’ – the system will prompt to fix an isolated user.
If you do not build Test’s login user, you can also use sp_change_users_login ‘auto_fix’, ‘test’, null, ‘testpassword’ – to create a login user name TEST, the password is the user with TestPassword. Ok, it is usually the case where the database object has been resolved. If there are multiple databases of the same user, you only need to select a different database, and the one of Update_one is executed.
Second, just open SQL Server Manager Studio, want to look at the information in the table, write a query statement, the result is a red line under the table name, and the object name is invalid when executed:
This is because there is no selected database name, because Test is just a table name, the database defaults to the Master database:
At this time, you can choose the database in the drop-down list.
Third, the cache problem, this is relatively simple, but should not happen when the database is just opened, and after several operations, the situation is the same, the solution is clear cache, shortcut Ctrl + Shift + R