Shulou(Shulou.com)06/01 Report--

1. Login permissions:

There are two authentication modes for SQL server:

1. Windows authentication mode:

When using Windows authentication, SQL server uses the Windows user and password in the operating system. That is, when the user's identity is authenticated by Windows, SQL server does not require a password and does not perform authentication. Windows authentication is more secure than SQL server authentication and protects SQL server from most attacks from Internet. Windows authentication is suitable for accessing databases within a local area network, such as an AD domain. It should be noted that when using Windows authentication, the corresponding user account must also be added to the list of login names in the database, otherwise the login will still fail, as shown below:

2. SQL server and Windows authentication mode (mixed mode):

SQL server and Windows authentication modes, also known as mixed modes, allow us to connect using Windows authentication or SQL server authentication. This mode is usually used when users running in a non-Windows environment, Internet users or mixed workgroup users access SQL server, for example, when external users access the database or cannot log on to the SQL server domain, if the SQL server authentication mode, the account and password used are stored in the SQL server database and have nothing to do with the users of the client.

Second, the permission setting of the database:

Access permissions in SQL server can be set at three levels: server, database, and object.

You can think of SOL Server as a building, which requires a pass to enter the building, which is the login account, and whether the type of pass (internal staff or guests) can enter the building mainly depends on the way the security guards check (that is, authentication mode). The database can be thought of as the room in the building. After entering the building, you can't access every room, and you still need the key of which room to access. This is the database user. Finally, each room contains some objects (such as chairs, computers. Think of them as tables in the database, and not everyone who enters the room has the right to use these objects. These objects are based on the schema, and can be assigned user rights to access the schema, and all the objects under the schema can be accessed through the framework.

Of course, if he is the owner of the room in this building, then he can have any object in the room, and he can even let others use them.

1. Server-level permission settings

A set of server roles (also known as fixed server roles) are built into SOL Server 2016 to perform server-level administration, such as creating databases, managing and auditing login accounts, aborting long-running processes in SQL Server instances, and so on, acting on the entire server rather than a single database.

A user can have multiple fixed server role permissions, but fixed server roles cannot be modified, added, or deleted. The server role is as follows:

2. Database-level permission settings:

Creating any database contains by default two special users, dbo (database owner) and guest, which is the owner of the database and has all permissions on the database, while the latter is a guest account that cannot be deleted, is disabled by default and does not have any permissions. Generally speaking, it is necessary to carefully enable the guest account and grant permissions.

The server role mainly controls the access of the server login name within the server scope, but it is relatively large in setting the management and operation permissions of the specific database. therefore, it is necessary to rely on the database role to divide the database-level objects into more detailed permissions. The database roles provided by SQL server by default are as follows:

The fixed database role also contains a role called public, which is used to capture all default permissions of users in the database. All users and roles belong to the public role by default, which cannot be deleted.

3. Object-level permission settings:

A database usually contains multiple data tables, views, stored procedures and other objects. if a user is given read permission to the database, the user can read all the tables under the database or try and so on. However, if you only want the user to have the permission to query an object, you need to set the permissions at the object level. The following figure shows some common permissions granted for the table and their descriptions:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.