[SqlServer]

The concepts of SqlServerLogin, User, and Schema are usually confusing and the specific differences are unclear. Here, I will sort it out to make it easy for you to check the records, and also for interested friends to check the records, hoping to help you. 1. The official explanation of LoginLogin is: Database Engine login name. (Msdn.microsoft.com

The concepts of SqlServer Login, User, and Schema are usually confusing and the specific differences are unclear. Here, I will sort it out to make it easy for you to check the records, and also for interested friends to check the records, hoping to help you. 1. The official explanation of Login is: Database Engine Login name. (Http://msdn.microsoft.com/

The concepts of SqlServer Login, User, and Schema are usually confusing and the specific differences are unclear. Here, I will sort it out to make it easy for you to check the records, and also for interested friends to check the records, hoping to help you.

1. Login

The official explanation of Login is:Database Engine login name.(Http://msdn.microsoft.com/zh-cn/library/ms189751.aspx) Login is a Server-level concept, that is, if you have a Login account and password, you can log on to the database engine of this Server. What does it mean to log on to the database engine? This means you can view the master, msdb, and tempdb databases, but you cannot view the model database. Sorry, you do not have any operation permissions for the databases you have created.

How can we operate databases? We need User.

The statement for creating Login is as follows:

--Create Login--Windows Authenticationcreate login [ReportLogin] from windows with default_database = [master],default_language = [us_english]--SQL Server Authenticationcreate login [ReportLogin] with password = N'123456',default_database = [master],default_language = [us_english]

2. User

User is a database object, not a Server Object. Therefore, to create a User, you must first enter the database to which you want to create the User (use this database) and then create the User.

The statement for creating a User is as follows:

--Create Useruse DBDemo;create user [ReportUser] for login [ReportLogin] with default_schema = ReportSchema

A User corresponds to a Login, and a Login can have multiple users. For the same Login, we can only create one User for a database. If we create an account again, we will get an error.

Note that we specified a default Schema when creating the User. What is this?

3. Schema

Translating Schema into Chinese is an architecture. The database architecture is to facilitate the management of database objects. Just as our own folders and directories are divided into three schemas: learning materials, music videos, and company documents, the default schema of the database is dbo. Database objects are managed by User-> Schema-> Object. this is very similar to the warehouse management method. Each pick-up person is associated with the cargo type first, which reduces the indexing of the cargo Code directly and greatly simplifies the operation process.

4. Role

The role concept is designed to reduce management costs and simplify management workload. If you want to grant permissions to 1000 users, you need to check the permissions for 1000 times. This is very cumbersome, so we only need to add these users to a role, grant this role permissions to all users.

Some common roles

Server Roles:

Bulkadmin

Dbcreator

Diskadmin

Processadmin

Public ---- default Server Role for each newly created login

Securityadmin

Serveradmin

Setupadmin

Sysadmin

Database Roles:

Db_accessadmin

Db_backupopeator

Db_datareader

Db_datawriter

Db_ddladmin

Db_denydatareader

Db_denydatawriter

Db_owner

Db_securityadmin

Public