SQL Server Permissions settings

With the development of the network and the increasing of hacker technology, the attack on SQL Server database is more and more, and the confidential data of many enterprises are threatened by some. How to enhance the security of SQL Server more convenient?

I. Security mechanisms for SQL Server

The security mechanisms of SQL Server mainly include the following four aspects:

(1) Client security mechanism: The user must be able to log on to the client, and then use the application or management tools to access the database, for Windows system customers, mainly related to the security of the operating system, mainly Windows account security

(2) The security mechanism of the server: when the user logs on to the server, an account and password must be used, and the server will determine the correctness of the account and password according to different authentication methods.

(3) security mechanism of the database: any account and password that can log on to the server corresponds to a default database, and SQL server uses the concept of "database user" for database-level rights management

(4) security mechanism of data objects: users can Access database data objects (such as tables, views) through the front three lines of defense, common access rights include data query, UPDATE, INSERT, delete

Second, login permission settings

There are two types of authentication modes for SQL Server:

Windows Authentication mode: logon with Windows users and passwords in the operating system, SQL Server does not require a password and does not perform authentication. Windows authentication uses the Kerberos security protocol, which is more secure than SQL Server authentication. For accessing databases within a local area network (such as an ad domain)

SQL Server and Windows Authentication mode: also known as hybrid authentication mode, allowing users to connect using Windows Authentication and SQL Server Authentication

Open SSMs, right-click on "SQL Server instance", select Properties, select Security, and you can set the authentication mode:

You can use SSMS to create an administrative login account, expand SQL Server instance, click Security, right-click Logins, new logins, as shown in:

Third, Access License permissions settings

Access permission settings can be set from server, database, object three levels, and SQL Server can be compared to a hotel, as shown in:

1. Server-level permission settings

A set of server roles are built into SQL Server to perform server-level management, such as creating databases, managing and auditing login accounts, and so on for the entire server, not a single database.

The steps to assign a user a server-level permission are as follows:

In SSMs, expand SQL Server instance, click Security, right-click a login, select Properties, select Server Role, set the permissions that you want to give the user, as shown in:

2. Database-level permission settings

Once you have established a SQL Server login account, you need to give the user permissions to manipulate the database. If you need to access a database, you need to establish the appropriate database user in the database, and the database user is mapped to the login account.

For example: to establish a user User2 on the MySchool database, the user corresponds to a login account User2, expand the database MySchool, security, right-click the user, select New user, open the Database-new window, make the relevant settings, As shown in the following:

The specific steps for setting the database level for a user are as follows:

In SSMs, expand SQL Server instance, click Security, right-click a login, select Properties, select User Mappings, set the permissions that you want to give the user, as shown in:

You can also create database roles when the database role does not meet the requirements

Expand Database MySchool, click Security, right-click Role, New Database role

3. Object-level permission settings

A database typically contains multiple data tables, views, stored procedures, and so on, to grant permissions to users on a single table

(1) Authorization for the table

Locate the table you want to authorize on SSMs, right-click the table, select Properties, permissions, click Search, add the user you want to authorize, and select the permissions you want to grant to the user below.

(2) authorizing the database

The database itself is also an object, and the authorization method is similar to the table, right-clicking the database, properties, permissions

(3) authorizing a stored procedure

Expand Servers, databases, select databases, programmability, stored procedures, properties, permissions

SQL Server Permissions settings