SQL Server2008 about "Invalid object" error

When we write the SQL language in SQL server2008, we often encounter the hint that the object is invalid. What is wrong with this, and why is there no such situation in version 2000? In fact, this is primarily the concept of a new architecture in the SQL Server 2005/2008 release. Here's a look at my understanding of the architecture in SQL server2008, in conjunction with online queries. There may be some things not in place to understand, you are welcome to point out, learn and improve together.

1. Definition

First, let's look at Microsoft's official definition of architecture: The schema is a collection of database entities that form a single namespace. A namespace is a collection in which the name of each element is unique. Personally, the definition is a bit obscure, and I understand that the architecture is the namespace in the database where the tables (database objects) are stored. The large namespace of a database can include multiple schemas (small namespaces), and each schema can contain multiple tables.

The schema actually existed earlier in SQL Server 2000, but in SQL Server 2000 the database user and schema are implicitly connected, and each database user is the owner of the schema with the same name as the user. When we use Query Analyzer to query a table, the name of a complete table should include the server name. Name of the database. The name of the user, and the fully qualified name of a table in SQL Server 2005/2008 should be the server name. The database name. The name of the object. The "User name" in the fully qualified name in SQL SERVER 2000 is also a user in the database, and is also the schema name.  If there is an account DF creates a table tb1 in the test database, the query statement that should be entered in Query Analyzer is select * from Test.df.tb1, that is, in SQL SERVER 2000 the schema to which a table belongs defaults to the login name of the creator of the table, and the user can modify all the database objects that he creates. However, in 2008, the user and its created object association were canceled, and a new architecture was added.

2. Introduction

So why did Microsoft introduce the architecture into the version after 05 and separate the user from the architecture? What is the benefit of separating the schema from the database user for administrators and developers?

1. Schema management is separate from user management. Multiple users can have the same schema through roles (role) or group (Windows groups) memberships. In SQL SERVER 2005/2008, each database role has its own schema, and if we create a table that specifies the schema name db_ddladmin, then any user who belongs to db_ddladmin can go to query, Modify and delete the tables that belong to this schema, but users who do not belong to this group are not allowed to manipulate the tables in this schema, it is important to note that members of the Db_dbdatareader group can view the tables in all databases, db_ Members of the Dbdatawriter group can modify tables in all databases, and members of the db_owner group can perform all operations on all tables in the database, and members of these groups can obtain special permissions in the database through roles.

2. When creating a database user, you can specify the default schema that the user account belongs to. (We recommend that you specify)

3. Deleting database users becomes extremely straightforward. In SQL Server 2000, the user and schema are implicitly associated, that is, each user has a schema with the same name. Therefore, to delete a user, you must first delete or modify all of the database objects owned by this user, such as an employee to leave to delete his account, but also to delete his created tables and views, etc., the impact is too large. When SQL SERVER 2005/2008 separates the schema from the object, there is no problem in deleting the user without renaming the objects contained in the user schema, and after removing the users who created the objects that the schema contains, you no longer need to modify and test the applications that explicitly reference those objects.

4. Sharing the default schema allows developers to create specific schemas for specific applications to hold objects, which is better than using only the administrator schema (DBO schema).

5. Setting permissions on the objects contained in the schema and schema (permissions) has a higher level of manageability than the previous version.

6. Distinguish between the objects of different business processing needs, for example, we can set the public table to the pub's schema, and the sales related to the set to sell, so it is easier to manage and access. Most users are accustomed to directly entering object names when creating objects and omitting the schema name of the object, in 2005/2008, the user creates a table with a default schema, and if the user does not set the default schema, the default schema is dbo, that is, if a db_ Ddladmin members create a table in the database that does not have the schema name, the full name of the table in the database should be dbo. Table name, the creator in the database if it is not a member of other special groups, you cannot make any modifications and queries to the tables created by special group members.

7. If you do not specify a default schema, then dbo, for forward compatibility, objects in earlier versions are migrated to the new version, and there is no schema concept in earlier versions. So the schema name of the object is the dbo. In SQL Server 2008, dbo is a schema

8. When looking for an object, look for objects under the same schema as the user's default schema, and cannot find the object to which the dbo is found

3, the role of architecture

User-schema separation allows objects in the database to no longer be tied to a user account, resolving the "user leaving the company" issue in SQL SERVER 2000 and previous versions, where the user who owns the object leaves the company, or leaves the job, It doesn't have to be a big hassle to change all of the user's objects to belong to the new user. In addition, when installing a package software, the database objects used to set up the software package belong to a specific schema, which is easy to distinguish. In other words, within a single database, objects of different departments or purposes can differentiate between different object naming principles and permissions through the schema.

In sqlserver2005/2008, schemas exist independently of the database user who created them. You can transfer ownership of a schema without changing the schema name. You can also create an object with a user-friendly name in the schema that explicitly indicates the functionality of the object. For example, you can create a schema named Cus.app.manifest.customEntry, in addition to Cus.app.entry.customEntry. Because "manifest" is not a user, you do not have to change this name after you remove the user from the database. This simplifies the work of database administrators and developers.

SQL Server 2005/2008 also introduces the concept of "default schema", which resolves the names of objects that are not referenced with their fully qualified names. In SQL Server 2000, you first check the schema that is owned by the calling database user, and then the schema owned by the DBO. In SQL Server 2005/2008, each user has a default schema that specifies the first schema that the server will search for when resolving the name of the object. You can use the Default_schema option of the CREATE user and alter user to set and change the default schema. If Default_schema is not defined, the database user will use DBO as its default schema. Here's a concrete example of how the architecture works.

4. Example

--command to manipulate the schema use mastergosetusergo--to create a test database creation Schtestgo Create login df with password= ' sj1234 ', default_ Database=schtestcreate login Xhl with password= ' sj1245 ', Default_database=schtestgouse schtestgo-- No schema was specified when creating two users create user DF for login DF Create user XHL for login xhl--This table does not specify which schema belongs to the default dbo schema gocreate table TB1 (name varchar (8), sex char (2))--This table belongs to the SCH schema Gocreate schema schgocreate table SCH.TB2 (name varchar (8), sex char (2), age int)-- Give DF this user permission to query objects in the SCH schema. Gogrant Select on Schema::sch to DF Gosetuser ' DF '--Switch User Dfselect * from tb2--this report "invalid object name" Because TB2 is now the default dbo schema, and TB2 is actually a sch Schema.--With the schema name, you can query the goselect * from Sch.tb2gosetuser--switch to SA---switch to xhlsetuser ' XHL '--cannot be queried because there is no permission select * from SCH.TB2 Gos Etuser---switch sa--to DF user Assign default schema alter user DF with Default_schema=schgosetuser ' DF '--toggle df--At this point, it is not necessary to specify SCH, if there are other objects in the schema, You can also query select * FROM Tb2gosetuser--switch sa--to create a third Test table, the same is the SCH schema under CREATE TABLE SCH.TB3 (ID int,uname varchar (8)) go--Switch User dfsetuser ' DF '---can query select * FROM Tb3go---but cannot insert data becauseNo insert permissions insert into TB3 values (1, ' ABCDE ')--Deny INSERT permission gosetuser--assign insertion permission grant inserts on Schema::sch to df--switch user Dfsetuser The ' DF ' go---data can be inserted into tb3 values (1, ' ABCDE ')--ok! ---query results select * FROM Tb3gogrant alter on Schema::sch to DF--enable DF This user has the ability to change the data objects in the schema. --Error--cannot grant, deny, or revoke permissions to the SA, dbo, entity owner, INFORMATION_SCHEMA, SYS, or yourself. Gouse mastergogrant control Server to DF--enables the user of DF to control the server. --Error--cannot grant, deny, or revoke permissions to the SA, dbo, entity owner, INFORMATION_SCHEMA, SYS, or yourself. Setusergouse schtestgo--Create Schema sch1create schema Sch1 go--Modify the schema of the Object TB2 table schema transferred from Sch to Sch1alter schema sch1 transfer sch.tb2go--Create a new user, specifying the default schema, default to dbo Create login yhy with password= ' sj1234 ', default_database=mastergouse Schtestgocreate user yhy for login yhy with Default_schema=sch--belongs to sch--switch user yhysetuser ' yhy '--query table, object name ' TB2 ' is invalid. SELECT * FROM tb2gosetuser--assign schema permission grant Select on Schema::sch1 to yhy--switch user yhysetuser ' yhy ' goselect * from TB2---or invalid because Not the same schemagosetusergoalter user yhy with Default_schema=sch1--Change yhy Default schema go--cannot query because Yhy is not the current userSELECT * FROM TB2-Display the current user goselect usergo--switch User yhysetuser ' yhy '-you can query now, if there are other objects in the schema, you can also query Goselect * from TB2--display  Current user Select Usergosetusergouse mastergo---Delete user drop Dfdrop user xhldrop user yhy---Delete login drop logins Dfdrop login Yhydrop Login xhl--Delete database drop db schtest

Reference:

http://blog.itpub.net/29512902/viewspace-1151745/

Http://blog.sina.com.cn/s/blog_3f2a8fa90100y2ki.html

https://msdn.microsoft.com/zh-cn/office/ms365789

https://msdn.microsoft.com/zh-cn/office/ms190387

https://msdn.microsoft.com/zh-cn/office/ms190401

SQL Server2008 about "Invalid object" error