If we want to know what the relationship is between the user and schema in the database, let's first look at what the user and schema are in the database. in SQL Server2000, user and schema always have an implied relationship because of the schema. It is very rare to realize that the user and schema are two completely different concepts, but in SQL Server2005 this architecture is broken and the user and schema are separated. First I will make a metaphor, what is database, what is schema, What is a table, what is column, what is row, and what is user. We can think of database as a large warehouse, the warehouse is divided into a lot of rooms, schema is one of the rooms, a schema represents a room, table can be considered as a bed in each schema, table (bed) is placed in each room, Can not be placed outside the room, it is not night to sleep homeless J. , and then the bed can be placed a lot of items, like a table can be placed many columns and rows, the database is the basic unit of storage data is table, the reality of each warehouse placed items in the basic unit is the bed, user is the owner of each schema, " So schema contains object, not user ", in fact, user is corresponding to the database (that is, user is the owner of each corresponding database), since there is the right to operate the database (warehouse), there must be Operation database every schema (room) of the right, This means that each database map user has a key to each schema (room). In other words, if he is the owner of a warehouse, then the use of the warehouse and everything in the warehouse is his (including the room), he has the full operation right, can throw away the unused things from each room, You can also put some useful things into a room, and of course you can remove a room (remove Schema). Oh, and the reality is too similar to it. I (the warehouse administrator) can also assign specific permissions to the user, that is, what he can do in a room, is only to see (read-only), or can be like the owner of all the control (R/W), this is to see the user's corresponding role roles, As for the issue of assigning permissions, I will leave it in detail in a separate blog later. Analogy to here, I believe we all know it. OK, let's continue our conversation. in SQL Server2000, if we create a user Bosco in a database, press the background also creates the default schema "Bosco" for us by default. The name of the schema is the same as the user's name, which is why we don't know the users and schemas. in SQL Server2005, for backwards compatibility, when you create a user with a sp_adduser stored procedure, SQL SERVER2005 also created a schema that is the same as the user name, but this stored procedure is reserved for backwards compatibility, and we should gradually become familiar with the new DDL language create user and create schema to manipulate the database. In SQL Server2005, when we create a database user with created user, we can specify an existing schema as the default schema for that user, and if we do not specify, the default schema for that user is dbo schema,dbo A room (schema) is like a large public room, with no default Schema for the current logged-on user, if you do something in a large warehouse, such as Create Tabe, if you don't specify a particular room (schema), Then your belongings will have to be put into the public dbo room (SCHEMA). However, if the current logged-on user has a default schema, everything is done on the default schema (for example, the current logged-on user is login1, and the user's default schema is Login1, Then all of the actions are done on this login1 default schema. The experiment has shown that it does). I guess you're going to be a little dizzy at this point. I just said dbo is a schema, but you can see in the database, dbo is also a user, dizzy bar, hehe. When you create a database in SQL Server2005, some default schemas are created, and the schemas created by default are: Dbo,information_ Schema, Guest,sys, etc. (and some role schemas, not mentioned, and dizzy). I already mentioned in the above, in the SQL Server2005 When you create a user with a stored procedure sp_adduser, SQL Server2005 also creates a default schema with the same username as the user name, and when we create table A, If there is no specific schema to prefix, this a table is created on which schema, that is, which room is entered. The answer is: if the user currently manipulating the database (which can be identified with select Current_User) has a default schema (specified when creating the user), then table A is created on the default schema. If the user currently manipulating the database does not have a default schema (that is, the default is null when creating user), but there is a schema with the same name as the user name, then table A is created on the DBO schema even if there is a schema with the same name as the user name. Since it is not the user's default schema, the creation of the table is not considered, as a general schema to deal with, although the name is the same, but there is no relationship oh. If a specific schema prefix is specified when creating table A, table A is created on the specified schema (with permissions). ）
Now that the problem has come out again, the user of the current operating database (see, again, highlighted in select Current_User) has no default schema, and when we use the CREATE TABLE a statement, a table looks for the DBO schema, And try to create it on the DBO schema, but if the user creating table A has only read-only permissions to the DBO schema, there is no write permission. This time a table is neither built nor unsuccessful, 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 manipulating the database (which is almost invisible to our eyes for a small amount of data), it is best to explicitly specify a specific schema prefix each time the database object is manipulated.
Now if the logged-on user is Sue and the user has a default Schema also for Sue, what is the order of the search for each room (Schema) If there is now a query statement for the SELECT * from MyTable? The order is as follows: First search the sys.mytable (SYS schema) and then search the sue.mytable (Default Schema) to search for the dbo.mytable (dbo schema)
The order of execution everyone is clear, then when querying the data in the database table, it is best to specify a specific schema prefix, so the database does not have to scan the SYS schema, of course, can improve the speed of the query.
Also, it is important to note that after each database is created, 4 schemas are required (deleted), and the 4 schemas are: Dbo,guest,sys and Information_schema, and the remaining schema can be deleted.
