SQL Server Architecture

In sqlserver 2005, you may often find such problems during work or study. You have created a table in the database using an account, however, it is depressing to find that the table you created does not have the modification and query permissions. This problem does not exist in sqlserver2000, so why does this happen in 2005? What are the benefits of this setting? In fact, this problem is mainly caused by a new concept-architecture in 2005.

    First, let's take a look at the definition of architecture in msdn: schema is a collection of database entities that form a single namespace. A namespace is a set. The names of each element are unique. Here, we can regard the architecture as a container for storing objects in the database.

The architecture actually exists in sqlserver2000. When we use the query analyzer to query a table, the name of a complete table should include the server name. database Name. user name. object Name, and the fully qualified name of a table in sqlserver2005 should be the server name. database Name. architecture name. object Name

In section 2000, if an account TT creates a table Table1 in the test database, the query statement on the server should be select * from test. TT. table1, that is, the structure of a table in sqlserver 2000 is the login name of the table creator by default. You can modify and modify all the database objects created by the table. However, in 2005, the association between the user and the architecture of the created object has been canceled, and a new architecture system has been added. The main advantages of this architecture are as follows:

1. Multiple users can have the same architecture through role or group member relationships. In MySQL 2005, the fixed database role in each database has its own architecture. If we create a table and name it db_ddladmin, therefore, any user in db_ddladmin can query, modify, and delete tables in this architecture. However, other users not in this group cannot, members of the db_dbdatareader group can view tables in all databases. Members of the db_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, the members of these groups can obtain special permissions in the database through the role.

2. It is extremely easy to delete database users. In SQL Server 2000, the user and architecture are implicitly associated, that is, each user has an architecture with the same name. Therefore, to delete a user, you must first delete or modify all the database objects owned by the user, for example, when an employee wants to resign and wants to delete his account, you have to delete all the tables and views created by the user. The impact is too large. After the architecture and object owner are separated, there is no such problem. Deleting a user has no impact on the database object.

3.Share the default architecture so that developers can apply for specific applicationsProgramCreating a specific architecture to store objects is better than using only the Administrator architecture (DBO schema.

4.Setting permissions on objects contained in architectures and architectures is more manageable than previous versions.

5.Differentiate the objects required for processing different businesses. For example, we can set the public table to the pub architecture and set the sales related to sales, which makes management and access easier.

    When creating an object, most users prefer to directly enter the Object Name and omit the schema name of the object. In 2005, a default schema will be added to the table created by the user, if you do not set your own default architecture, the default architecture is DBO. That is to say, if a member of db_ddladmin creates a table without the schema name in the database, the full name of this table in the database should be DBO. table name. If the creator is not a member of another special group in the database, he/she cannot modify or query the table he/she created, it is equivalent to saving the money you earned into others' bank cards, but you cannot get it.