Relationship between login, schema, user, and role in SQL Server

I believe that many people will be dizzy when they see these objects for the first time. Today I will lead you to combine these concepts.

· Login

Login is a server-level concept that indicates the credential for logging on to the server. For example, if there is a database da on server_a, you want to access the database da, the first step to do is to first log on to hosting the database server_a, the database administrator can use the following T-SQL statement to create a login, temporarily named reportlogin.

O Windows Authentication:

Create login [reportlogin] from windows with default_database = [Master], default_language = [us_english]

O SQL Server Authentication:

Create login [reportlogin] with Password = n' 123456 ', default_database = [Master], default_language = [us_english]

After installing the database software, there will be some pre-created logins, which are well known as SA and buildin \ administrators.

· User

With a login, you can log on to the server of the hosting database. It does not indicate that you can access the database. Unless you are a built-in Windows administrator, you can only log on to the server, nothing can be done, just like you have a login key for a private small courtyard. You open the door (LOGIN) And you can only stand in the yard, because the door of the room is locked, you do not have the room key (User). How can you enter the room? User is to complete such a function. We can use the following T-SQL statement to create a user, temporarily named reportuser, because the user belongs to the database object, not to the server object (LOGIN belongs to the server object ), therefore, to create a user, you must first enter the database for which you want to create the user (use this database) and then create the user.

Create user [reportuser] For Login [reportlogin] With default_schema = reportschema

Go

We can see through the statement that every user must be mapped to a login, so the relationship here is:

· Each user must correspond to a login.

· Each login can correspond to multiple users, provided that the user is in different databases.

Once we create a user mapped to a login, if we use this login to log on, we can access the database with this user. For example, we opened the door with login, And we entered the yard. Now we have a user, which means we still have the key to a room door. Now we can open the room door (database ), he entered the small room in an elegant place.

After installing the database software, there will be some pre-created users, which are well known as DBO, guest, sys and so on.

Look at the T-SQL statement carefully, we will find that there is a default schema? Next, let's look at it.

· Schema

After the strength of the two tigers, we finally entered our room as expected, but we found that there were both upper and lower bunk beds. What is my schema? Where can I put my luggage? This is the architecture. Schema: the database architecture is a non-repetitive namespace independent of database users. You can regard the architecture as an object container. I believe that everyone is familiar with the concept of containers. For example, if a new hard disk is used, we will divide it into N drive letters, such as C:, D:, E:, etc, under each drive letter, we have n folders folder, which are containers. For another example, we can use a website as an analogy. A website contains many web pages, images, and script files. Let's just call it a website object. Obviously, it is impossible for us to put all the website objects under a folder. Similarly, database objects cannot be put in a pot like boiled dumplings. For websites, we usually place files of different modules in different subfolders. Who is the folder for storing database objects? The answer is schema ).

In fact, every object in the database has an architecture. Every object in the database has a place, but many of us didn't specify it when creating the database object. So where are they? In Versions later than SQL Server 2005, when we create a database object without explicitly specifying a schema, the database will specify a default Schema: DBO by default. All database objects that do not explicitly specify the schema are stored in the DBO public space.

The schema concept is introduced to solve the disadvantages of too many database objects and poor management.

After the database software is installed, there will be some pre-created schemas, including DBO, and each schema named after the predefined role, such as db_owner, db_datareader, db_datawriter, and so on.

· Role

The role concept is designed to reduce management costs and simplify management workload. Imagine if a company has 1000 people, everyone needs to write permissions on a table. If there is no role, it takes a long time for us to grant permissions to these 1000 individuals, that is, simply run copy. What if there are 3000 employees in the company? It is estimated that the Administrator has quit. A role occurs to solve this problem. When you create a role, the database administrator only needs to grant the role the write permission on the table, the next step is to assign the role to the employee who needs to have write permission on the table in the company. Does the management cost decrease a lot.

After the database software is installed, some pre-created server role and database role (beginning with DB _) are available ).

O Server roles:

1. bulkadmin

2. dbcreator

3. diskadmin

4. processadmin

5. Public ---- the default server role for each newly created Login

6. securityadmin

7. serveradmin

8. setupadmin

9. SysAdmin

O database roles:

1. db_accessadmin

2. db_backupopeator

3. db_datareader

4. db_datawriter

5. db_ddladmin

6. db_denydatareader

7. db_denydatawriter

8. db_owner

9. db_securityadmin

10. Public

For details about the permissions of each role and what the role can do, refer to book online. Microsoft idea is a database pre-installed role that can complete most of the permission allocation issues.

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/yanjiangbo/archive/2009/08/26/4486222.aspx