SQL Server R2 Database logins, users, schemas, permissions, and Roles

　　Opened Blog Park has been a period of time, has not written a blog, one is busy work, second, has been thinking about how to write a blog, the third is the knowledge technology is not home, dare not write fraught ah. At first I was thinking about writing about. NET blog, the first article is about the database. My first contact with the database is still in the classroom, when the teacher said the knowledge point is very simple is to increase, delete, change, check. Feel database no technical content, nothing more than to look up data to change the data, it is so simple, nor the database as a technology to go into deep research. has been to work, as the requirements of the project continues to increase, the requirements for data security more stringent, so the study of the database has deepened, only to feel that the database is very profound, really need to take the time to learn to study.

This period of time just idle down, put these days on the data to understand some of the knowledge points, I am also a novice, of course, the knowledge of the data is superficial, there is no place to look at you more advice.

these days first studied the security, permissions and other aspects of the database, that is, first of all, the issue of database security and permissions, The first is a simple introduction to the login, user, and schema of the database. Logins everyone knows the name that is used to log in to the database, such as the default login SA for the database, and if you do not change the permissions of the sa login, the sa login has full access to the database for all functions. So what is a user name? For the sa login, when you log in to the database with SA and create a new database, expand the security under the database-users can see the DBO, SYS, guest and other user names, and the sa login all operations of the user is the DBO, this is the database default. That is, the SA is the login name, and the user name after login is dbo. So what about the architecture? We all know that when we log in to the database with SA and create a table userinfor, you can see the dbo when you expand the database. Userinfor, this dbo is the schema name, not the user name, at first I did not understand the online search for some information, some say dbo. Userinfor is the user name, what exactly is it? Let's do a test here.

Restrictions on permissions can be implemented using logins in conjunction with the user, and I'll first create a login named me. First, let's look at the database Security node first.

　　

We can see the sa login with our login, and then right-click on the login name to create a new login me.

　　

　　

This place, we log in with SQL Server authentication, the server role bar only tick public, status bar Select Grant and enable, the specific settings are said later. Ok. Then look at the login honour point to see the newly created login me.

　　

Then we create a database UserData, and then for this database we created a new schema medbo in the database's security node's schema.

　　

Fill in a schema name as follows:

　　

OK, and then create a user name for the database on the user node Mename, here is mainly to make a distinction with the schema.

　　

　　

Note here that the login name to choose Me, the default schema medbo, user-owned schema to tick the schema just created, role members selected db_owner. OK, you can see the user name and schema you just created.

　　

Then go to security node, expand User name find me user right-click Property, User mapping option, you can see

　　

Here the login and username and schema are all created well, so how to use it, we can use the login name I just created to log back into the database, create a table userinfor in the UserData database.

　　

You can see that the newly created table is medbo. Userinfor, not mename. Userinfor. This shows that the table created after logging in with SA represents dbo. Table This dbo is a schema, not a user name.

So how do you set permissions and roles? Then we go back to the properties of the Me login, and look at the server role options,

　　

You can see that there are multiple service-period roles, and just when we created the me login, only the public was checked, what does each role represent? Here's a look at this.

　　Bulkadmin: Members of this server role can run BULK INSERT statements

Dbcreater: You can create, change, delete, and restore any database

Diskadmin: Managing disk files, such as mirrored databases and adding backup devices

Prosessadmin: Multitasking can perform multiple tasks by executing several processes

Securityadmin: Managing Logins and properties

ServerAdmin: You can change server-wide configuration options and shut down the server

Setupadmin: User settings for stored procedures that require management of linked servers and null values, can add, configure, and remove linked servers, and can be null-valued for startup processes

SysAdmin: Perform any task

Public: No permissions at the initial state, all databases have his members

Then look at the user mapping option

　　

There are also multiple roles, each of which has the following roles:

　　DB_Owner: Take any action in the database

Db_accessadmin: Adding or removing users from the database

Db_backupopperator: Allow Backup of database

Db_datareader: Allows data to be read from any table

Db_datawriter: Allow data to be written to any table in the network

Db_ddladmin: Allows you to add, modify, and delete any object

Db_denydatareader: Denies viewing any data, but can be viewed through stored procedures

Db_denydatawriter: Refuses to modify any data, but can be modified through stored procedures

Db_securityadmin: You can change permissions and roles in the database

Public: Each database role belongs to the public role, and when a user has not been granted or denied specific permissions to a securable object, the user will be granted the public role permission for the securable and cannot be deleted.

One more thing, the user mapping of the Me login attribute we only checked the UserData database, the other is not checked, if we log in to the database with the Me login and then access the data created by the sa login name, this is inaccessible:

　　

As you can see, the login name and permissions work.

Here, the new login and user name, as well as the Configure permissions role is over, here is simply the login name of the data, the user name and the schema and permissions are simply introduced and used. You can choose to create different logins and usernames, as well as permissions and roles, depending on what you need in a specific project. The above is said wrong or there is no place to look at more advice.

SQL Server R2 Database logins, users, schemas, permissions, and Roles