The relationship between user and schema in the database

If we want to know the relationship between user and schema in the database, we must first understand the concept of user and schema in the database.

In sqlserver2000, because of the architectural reasons, the user and schema always have a layer of implicit relationship, so we rarely realize that the user and schema are two completely different concepts, but in sqlserver2005 this architecture has been broken, The user and schema are also separated.

First to do a metaphor, what is database,schema,table, column, line, user. The database can be regarded as a large warehouse, the warehouse is divided into a lot of rooms, the schema is the room, a schema represents a room, table can be regarded as a bed in each schema, table (bed) is placed in each room, Cannot be placed outside the room, it is not night sleep homeless ah. Then the bed can be placed a lot of items, like table can be placed a lot of columns and rows, the database is stored in the basic unit of data is a table, in reality each warehouse place the basic unit of the item is the bed, user is the owner of each schema, (so the schema contains the object, Instead of user), the user is corresponding to the database (that is, the user is the owner of each corresponding database), since there is the right to operate the database (warehouse), there must be operational database of each schema (room) right, That is, each database mapping user has a key to each schema (room), in other words, if it 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, can also put some useful things into a room, the reality of the similar, I can also assign the user specific permissions, that is, what he can do in a room, is only to see (read-only), or can be like the owner has all the control (R/W), This depends on the role of the user.

In sqlserver2000, if we create a user Bosco in a database, then the background also for our default creation of the schema "Bosco", the schema name and user name is the same, which is why we can not tell the user and schema.

In sqlserver2005, for backwards compatibility, when you create a user with a sp_adduser stored procedure, sqlserver2005 also creates a schema that is the same as the user name, but this stored procedure is reserved for backwards compatibility. We should gradually become familiar with the new DDL language create user and create schema to manipulate the database, in sqlserver2005, when we create the database user with the creation user, We can use this user to specify an existing schema as the default schema, if we do not specify, then the default schema for that user is the dbo schema,dbo Room (schema) is like a large public room, If there is no default schema for the currently logged-on user, if you do something in the warehouse, such as CREATE TABLE, if you do not have a specific room (schema), then your items will have to be put into the public dbo room (schema). However, if the current logged on user has a default schema, then everything is done on the default schema (such as the current login user is login1, the default schema for the user is login1, So the operation is done on this login1 default schema. The experiment has proved to be true). Estimated that at this time you will be a little dizzy, just said dbo is a schema, but you can see in the database, the DBO colleague is also a user, dizzy.

When creating a database in sqlserver2005, there will be some schema included, including the schema: Dbo,information_schema,guest,sys and so on (there are some character schemas).

As already mentioned above, when creating a user with stored procedure sp_adduser in sqlserver2005, sqlserver2005 also creates a default schema with the same user name for us, and this problem comes out when we create Table A, if there is no specific schema to prefix, the A-table created on which schema, that is, which room entered. The answer is:

1. If the user of the current operational database (which can be identified with select Current_User) has a default schema (specified when created), table A is created on the default schema.

2. If the user of the current operational database does not have a default schema (that is, it is empty by default when creating user), but there is a schema with the same name as the user name, 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 default schema for the user, 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.

3. If you specify a specific schema to prefix when creating table A, table A is created on the specified schema (with permission). ）

Now the problem is out, in the current operation of the database user (with select Current_User can see, re-emphasize) without the default schema, when we use the CREATE TABLE a statement, a will look for the DBO schema, and tries to create it on the DBO schema, but if the user who creates the a table has only read-only permission to the DBO schema, and does not have permission to write. At this time the A-table is neither established nor successful, this is the relationship between the Login,user,role and schema four mentioned here, in order to avoid confusion and increase the speed of the operation of the database (in a small amount of data, for our naked eye can hardly see the difference), It is best to explicitly specify a particular schema prefix every time you manipulate database objects.

Now if the logged-on user is Sue, the user has a default Schema also for Sue, so if there is now a query statement for SELECT * FROM MyTable, then what is the order of the search for each room (Schema)? The order is as follows:

1. First search sys.mytable (Sys Schema)

2. Then search for sue.mytable (Default Schema)

3. Last Search dbo.mytable (dbo Schema)

Order of execution since everyone is clear, then in the Query database table data, it is better to specify a specific schema prefix, this way, the database does not have to scan the SYS schema, of course, can improve the speed of the query.

It is also necessary to note that, after each database is created, there are 4 schemas that are necessary (delete is not removed), the 4 schemas are: Dbo,guest,sys and Information_schema, the rest of the schema can be deleted.