SQL-1. Distinguishing between login, user, schema, and role

   Login

Login is a server-level concept, indicating credentials to log on to the server, such as a database da on server_a, then to access the Database DA, the first thing to do is to log on to hosting the database server_a, The database administrator can use the following T-SQL statement to create a login, which is named Reportlogin.

• Windows Authentication:

Create Login [Reportlogin] from windows with default_database = [Master],default_language = [us_english]

• SQL Server Authentication:

Create login [Reportlogin] with password = ' abc***123456abc ', default_database = [Master],default_language = [us_english]

The following statement creates SQL Server-validated [Reportlogin] as a mandatory password policy, the default database is master, the default language is English, and Server roles is the default public in Public,user mapping. Securables is the current server, and the permission setting is set to connect to SQL and view any database, which is the SA that gives it permission to connect to the databases.

After installing the database software, there will be some pre-created Login, the more famous is SA and buildin/administrators.

User

With a login, you can log in to hosting the database server, does not indicate that you can access the databases, unless you are a built-in Windows administrator, otherwise, you can only log on to the server, nothing to do, like, You have a private house door key (login), you open the door (log in), you can only stand in the yard, because the room door is locked, you do not have the room key (User) Ah, how to enter the house inside? This is what the user wants to accomplish. We can use the below T-SQL statement to create a user, named Reportuser, because the user belongs to the database object, does not belong to the server object (login belongs to the server object), so if we want to create a user, You must first enter the database where you want to create the user (use the database), and then create the user.

Create user [Reportuser] for login [Reportlogin] with default_schema = Reportschema

Go
Note: The last Default_schema is the first schema that the specified server will search for when resolving object names for this database user.
We can see from the statement that each user is mapped to a login, so the relationship here is:

· Each user must correspond to a login.

· Each login can correspond to more than one user, provided the user is in a different database.

Once we have created a user mapped to a login, at this point, if we log in with that login, we can access the database that owns the user, continue to analogy, we opened the door with login, we entered the small yard, at this time we have a user, It means that we have the key to the door of a room, so we can open the room door (database) and enter into this small room openly.

After installing the database software, there will be some pre-created user, the more famous is Dbo,guest,sys and so on.

Schema

After the Dickens, we finally got the wish to enter our room, but found that the original inside are up and down the shop, the bed (Schema) is my? Where can I put my luggage? This is the schema: the database schema is a non-repeating namespace that is independent of the database user, and you can treat the schema as a container for the object, which is not the bed, put things. Believe that the concept of container people are already familiar with, such as a new hard disk, we will divide him into N disk characters, C:,d:,e: Ah, and so on, under each drive, we have n folder folders, these are containers. For example, we can take a website to do analogy, a website contains a lot of pages, pictures, script files, we call it the site object. Obviously, we can not put all the site objects under a folder, the same reason, database objects can not be like cooking dumplings in the database such a pot out. For Web sites, we usually put different modules of the files in different sub-folders, then who is the folder to store database objects? The answer is: Architecture (Schema).

Virtually every object in the database has a schema, where each database object is placed, but many of us do not specify when creating database objects. In versions after SQL Server 2005, when we created a database object that did not explicitly specify a schema, the database specifies a default schema:dbo for us. All database objects that do not explicitly specify a schema are placed in the dbo public space.

The introduction of schema concept is to solve the shortcomings of too many database objects that are not well managed.

After installing the database software, there are pre-created schemas, including dbo, and each schema named after the predefined role, for example: Db_owner,db_datareader,db_datawriter

Wait a minute.

Role

The concept of role is to reduce management costs and simplify the workload of management to appear, imagine if a company has 1000 people, everyone needs to write to a table permission, if there is no role, we have to the 1000 people respectively grant permissions, Just copy run, also need a long time ah, if the company has 3000 people? It is estimated that the administrator has already quit. Role is to solve this problem. Set up a role, the database administrator only with grant this role to have write permission to this table OK, the next step is to do the company inside need to have write permission to the table employees assigned to this role can be, the management cost is not much lower ( is to put a batch of required permissions in a role, and grant this role to a group of user).

After the database software is installed, there are pre-created server role and database role (beginning with db_).

Server Roles:

1. bulkadmin

2. dbcreator

3. diskadmin

4. processadmin

5. Public----The default server Role for each new login

6. securityadmin

7. serveradmin

8. setupadmin

9. The sysadmin

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

Public

as to how much permissions each role has, what can be done by each role, see Book Online, Microsoft's idea is that the role that the database is preloaded with can accomplish most of the permissions assignment issues.

SQL-1. Distinguishing between login, user, schema, and role