If we want to know the relationship between user and schema in the database, Let's first look at the concept of user and schema in the database. In SQL Server, due to the architecture, there is always an implicit relationship between user and schema, which makes us seldom realize that user and Schema are two completely different concepts, however, in SQL server2005, this architecture is broken, and the user and Schema are also separated. First, let me make a metaphor: What is database, What Is schema, what is table, what is column, what is row, and what is user? We can regard the database as a big warehouse. The Warehouse is divided into many rooms. The schema is the room, a schema represents a room, and the table can be seen as the bed in each schema, the table (bed) is placed in every room, and cannot be placed outside the room. Isn't it because I went to bed at night and became homeless? J ., Then many items can be placed on the bed, just like many columns and rows can be placed on the table. The basic unit for storing data in the database is table. In reality, the basic unit for storing items in each warehouse is the bed, the user is the master of each schema. [Therefore, the schema contains objects rather than users.] In fact, the user corresponds to the database (that is, the user is the master of each corresponding database ), since you have the right to operate databases (warehouses), you must have the right to operate each Schema (room) in the database. That is to say, the user mapped to each database has the key for each Schema (room, in other words, if he is the master of a warehouse, his right to use the warehouse and everything in the warehouse is his (including the room), and he has full right to operate, you can discard unnecessary things from every room, or place some useful things to a room. Of course, you can also remove a room (remove schema ). Oh, it's similar to reality. I (the Warehouse administrator) can also assign specific permissions to users, that is, what they can do when they arrive in a room ), you can still have the same control (R/W) as the owner. This depends on the role corresponding to the user. As for permission allocation, I will stay in my separate blog for details. I believe everyone knows this. OK. Let's continue! In SQL Server2000, if Bosco is created in a database, the default schema [Bosco] is created in the background ]. The schema name is the same as the user name, which is why we cannot clearly identify the user and schema. In SQL server2005, for backward compatibility, when you use the sp_adduser stored procedure to create a user, SQL server2005 also creates a schema with the same user name, however, this stored procedure is retained for backward compatibility. We should gradually become familiar with using the new DDL language "create user" and "create schema" to operate databases. In SQL server2005, when we use create user to create a database user, we can specify an existing schema as the default schema for this user. If we do not specify an existing schema, the default schema of the user is DBO schema. The schema of the DBO room is better than a large public room. If the current login user does not have the default schema, if you perform some operations in a large warehouse, such as create tabe, if you do not specify a specific room (schema), then your items will have to be put into the common DBO room (schema. However, if the currently logged-on user has a default schema, all operations are performed on the default Schema (for example, if the currently logged-on user is login1 and the user's default schema is login1, all the operations are performed on the default schema of login1. The experiment has proved so ). It is estimated that you will be a little dizzy at this time. Why? I just mentioned that DBO is a schema, but you can view it in the database. DBO is also a user, so it's dizzy. When creating a database in SQL server2005, some default schemas will be created. The default created schemas include DBO, information_schema, guest, sys, and so on (there are also some role schemas, ). As mentioned above, when using the Stored Procedure sp_adduser to create a user in SQL server2005, SQL server2005 also creates a default schema with the same user name, in this case, the problem arises. When we create Table A, if no specific schema is used as the prefix, the schema in which table A is created, that is, the room in which table A is entered? The answer is:
- If the current database user (which can be identified by select CURRENT_USER) has a default Schema (specified during user creation), table A is created in the default schema.
- If the current database user does not have a default Schema (that is, it is blank when the user is created), but there is a schema with the same name as the user name, therefore, Table A is created on DBO schema. Even if a schema with the same name as the user name exists, it is not the default schema of the user, therefore, it is not considered when creating a table. It is processed as a normal schema. Although the name is the same, it does not matter.
- If a specific schema is specified as the prefix when table A is created, Table A is created on the specified schema (do you have the permission ?)
Now the problem is solved again. If the current database user (which can be viewed using select CURRENT_USER, once again stressed) does not have the default schema, when we use the create table a statement, table A searches for the DBO schema and tries to create it on the DBO schema. But what if the user who creates Table A has only the read-only permission on the DBO schema but has no write permission? At this time, Table A is neither established nor established. This is the relationship between login, user, role, and schema that I will mention later. Here, in order to avoid confusion and improve the speed of Database Operations (within a small range of data, there is almost no difference to the naked eye ), we recommend that you explicitly specify the most prefix of a specific schema when operating database objects.
If the login user is Sue and the user has a default schema that is also Sue, if there is a query statement that is select * From mytable, then search for each room (schema) what is the order? The order is as follows:
- First, search for SYS. mytable (sys schema)
- Search for Sue. mytable (default schema)
- Finally, search for DBO. mytable (DBO schema)
Since the execution sequence is clear, it is better to specify a specific schema prefix when querying data in the database table in the future. In this way, the database does not need to scan the Sys schema, of course, the query speed can be improved.
Note that after each database is created, four schemas are required (and cannot be deleted). The four schemas are DBO, guest, sys and information_schema, and other schemas can be deleted.
