Login name, role, database user, role, and schema of the database management system

Overview

One of the previous blogs has written about server logins, server roles, database users, database roles, and some knowledge of the theory, so you can look at this blog: logins, server roles, user names, and database roles. This blog focus on the operation.

Database Schema

in the specific operation, the first to popularize the knowledge of the database architecture, it should be noted here that the database schema is not for the database topology, but for the database management system, for the database of a function.

For example, the benefits of architecture and architecture, database for a building, then the structure is a floor, a floor of the house is a table, the table generated by the stored procedures, triggers and so on are also belong to this architecture, will be a floor rent to many people to live, these people in this layer of building casually toss, but these people go, he toss things do not need to change , later people can again on the basis of the toss, because the predecessors toss out of things belong to this architecture, things belong to the architecture is not a person, so, after the people have gone, things do not need to make corresponding changes.

Framework: A container that holds database objects, objects within the framework must be unique, the names of objects of different frames can be the same, and a database has multiple frames. In the previous (SQL 2000) database object access, The server name is used. The name of the database. The name of the table, followed by the database access method is this, the server name. The name of the database name. The advantage of this is that a user resigns, I can delete the user directly, but, before it is not possible, because the database object is found through the user name, such a A change is very troublesome. Of course, the framework does more than that, such as: The framework restricts user access to the other framework tables of the database. Well, the theory of things to this end, the following start combat.

Create a database system login name

Create 3 system logins, student, teacher, and person, respectively, as

Create a database

Create DATABASE Databasenamego

Create a database schema

Create 3 schemas with the following names: Teacher_kj, student_kj, and person_kj, as

Create a database role

Create 2 database roles, the role name is: Teacher_js and Student_js, respectively, the schema name above the object, the specific corresponding to see, such as: Teacher_js corresponding TEACHER_KJ, specific operations such as

Create a database user

Create 3 users, Teacher_user, Student_user, and Person_user, where teacher_user and student_user belong to roles, no specific architecture, person_ User does not belong to the created role, owns PERSON_KJ, and the specific actions such as

Create a table under the appropriate schema

Use Databasenamegocreate table [teacher_kj]. TABLENAME1 (ID int primary key,name varchar NOT NULL) gocreate table [student_kj]. TABLENAME1 (ID int primary key,name varchar NOT NULL) gocreate table [person_kj]. TABLENAME1 (ID int primary key,name varchar NOT NULL) gocreate table TableName1 (ID int primary key,name varchar (a) NOT NULL )

Final Result

There is a picture of the truth, for the final result diagram

Summarybelow is a summary chart, not I painted, but very classic, so directly to use the

SQL Server is very powerful, for example, through the SQL Agent service, which is the proxy server for many other things, such as: jobs, maintenance plans, send mail, and of course, before doing this, it is best to back up and restore the database (full backup, Differential backups, log backups), because one of the functions of the SQL Agent service is to automate backup and restore.