Understanding of schemas in SQL SERVER 2005/2008 (i)

Reference: http://www.cnblogs.com/chillsrc/p/3383098.html

In a real-world operation, in SQL SERVER 2008, a new user has been created to access several tables that have been made by another user, but cannot be queried, prompting the object name ' Customentry ' to be invalid. ”。 When the schema name is brought in, such as "Cus. Customentry "), but can query the data in the table, but the query statement is already written dead in the application, if you want to make changes, there is a lot of work, this is a very depressing thing. So I want to solve this problem from the database level, after querying a large amount of data, the schema in SQL Server to understand, and solve the above problems.

The following is an understanding of the architecture in SQL SERVER and is documented here for your reference.

There is no problem in SQL SERVER 2000, so why is this happening in 2008 and what are the benefits of such a setting? The main reason for this problem is that there is a new concept-Architecture in SQL SERVER 2005/2008.

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. Here, we can look at the schema as a container for the objects in the database.

The schema is actually already present in SQL Server 2000, where database users and schemas 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 name of the database.

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.

Benefits of User Architecture separation

How does separating the schema from the database user benefit 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, the fixed database roles in each database have a schema of their own, and if we create a table and give it the schema name db_ddladmin, then any user belonging 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_ The members of the Ddladmin create a table in the database that does not have the schema name, and the table is complete in the database The name should be dbo. Table name, the creator in the database if it is not a member of other special groups, you can not create a table to make any changes and queries, it is equivalent to the money you earn into someone else's bank card, but not to take out.

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, find the object under the same schema as the user's default schema, and cannot find the object to find the dbo again

The 8th is a bit difficult to understand, we look at a picture, through this picture, you should be able to show the understanding of this point:

Understanding of schemas in SQL SERVER 2005/2008 (i)