管理 MSSQL Server 2022 中的数据库、用户和权限

Microsoft SQL Server通常称为MSSQL Server，是由Microsoft Corporation开发的关系数据库管理系统(RDBMS)。它用于存储和管理大量数据，可用于各种应用程序，包括基于 Web、客户端/服务器和独立应用程序。

MS SQL Server 可用于企业部门，包括金融服务、电子商务和医疗保健。这主要是因为它能够处理大量并发用户，并为存储和管理敏感数据提供安全可靠的平台。 MSSQL还支持大量编程语言和平台，例如.NET、Java、Node.js等。这使其成为可用于广泛应用程序的多功能数据库平台。

MSSQL 提供了一系列功能，包括：

• 可扩展性：它可以处理大量数据并进行扩展以适应不断增长的用户和应用程序数量。
• 安全性：它提供强大的安全功能，包括加密、访问控制和审核，以保护数据并确保遵守法规。
• 高可用性：它包括集群和复制等功能，以确保高可用性并最大限度地减少停机时间。
• 集成：MSSQL 与多种编程语言和平台集成，包括 .NET、Java 和 Node.js，使开发和部署应用程序变得轻松。
• 商业智能：它还包括高级分析和报告功能，例如数据挖掘、数据仓库和OLAP（在线分析处理），使用户能够从大量数据中获得见解。
• 性能：它提供查询优化和索引等功能，以确保快速高效的数据检索和处理。
• 云能力：MSSQL可以部署在本地或云端，支持混合云场景。

跟我一起阅读本指南，我们将深入探讨如何在 MS SQL Server 2022 中管理数据库、用户和权限。

入门

在我们深入讨论这个问题的核心之前，您需要在系统上安装 MSSQL Server 2022。根据您的环境，可以通过多种方式完成此操作。以下是一些帮助您安装 MSSQL Server 2022 的专用指南。

• 在 Docker/Podman 容器中运行 Microsoft SQL Server 2022
• 在 Rocky Linux 8/AlmaLinux 8 上安装 Microsoft SQL Server 2022
• 如何在 Ubuntu 20.04 上安装 Microsoft SQL Server 2022

安装完成后，如下图所示

1.安装SQL Server命令行工具

要管理 MS SQL Server 2022 上的数据库，可以使用各种工具，包括 SQL Server Management Studio (SSMS)、Azure Data Studio 和命令行工具。

在本指南中，我们将学习如何使用命令行管理 MSSQL Server 2022 数据库。我们将安装 SQL Server 命令行工具：sqlcmd 和 bcp。

根据您的系统，使用以下命令添加存储库并安装 sqlcmd 和 bcp。

• Debian/Ubuntu

首先将存储库添加到系统中。

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

添加后，更新您的系统并安装 sqlcmd 和 bcp：

sudo apt-get update
sudo apt-get install mssql-tools unixodbc-dev

现在导出环境PATH：

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

• 在 Rocky Linux/Alma Linux/CentOS/RedHat

使用以下命令添加存储库：

curl https://packages.microsoft.com/config/rhel/8/prod.repo | sudo tee /etc/yum.repos.d/msprod.repo

添加后，安装 sqlcmd 和 bcp 软件包：

sudo yum remove mssql-tools unixODBC-utf16-devel
sudo yum install mssql-tools unixODBC-devel

现在导出环境PATH

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

• 在 SLES 上

还可以在 SUSE Linux 上安装 mssql-tools。首先添加存储库：

sudo zypper addrepo -fc https://packages.microsoft.com/config/sles/12/prod.repo
sudo zypper --gpg-auto-import-keys refresh

添加后，使用以下命令安装 mssql-tools：

sudo zypper install mssql-tools unixODBC-devel

导出环境：

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

• 在 MacOS/OS-X 上

您可以使用 HomeBrew 在 MacOS/OS-X 上安装 sqlcmd 和 bcp。使用以下命令添加存储库：

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release

接下来，使用以下命令安装 mssql-tools：

brew update
brew install mssql-tools

2.管理MSSQL Server 2022上的数据库

现在我们可以使用Transact-SQL语言语句来管理MSSQL Server 2022。首先，使用以下语法的命令访问数据库：

sqlcmd -S IP_ADDRESS -U sa -P '<YourPassword>'

在命令中，有几个标志：

• -S： 指定 SQL Server 名称/IP 地址
• -U：设置用户名
• -P ：提供用户密码

MSSQL 上的默认用户是SA，密码是在安装过程中提供的。下面是一个执行示例。

连接到 MSSQL Server 2022 后，您现在可以管理数据库。

A。在 MSSQL 上创建数据库

要在 MSSQL Server 2022 上创建数据库，请使用具有以下语法的命令：

CREATE DATABASE [database_name];

例如：

CREATE DATABASE sampledb;
GO

创建数据库后，您可以使用以下命令列出数据库：

SELECT Name from sys.databases;
GO

示例输出：

在 MSSQL 上创建表

现在在上面的数据库上，我们可以添加表。对于此演示，我将添加一个名为 my_table 的简单表：

USE sampledb;
CREATE TABLE my_table ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT );
GO

要将数据插入表中：

INSERT INTO my_table (id, name, age)
VALUES (1, 'John', 25)
GO

查看表中的数据：

SELECT id, name, age FROM my_table
GO

示例输出：

删除 MSSQL 上的数据库|表

您还可以删除 MSSQL 上的数据库或表。要删除表，请使用具有以下语法的命令：

DROP TABLE [table_name]
GO

例如;

DROP TABLE my_table;
GO

要删除数据库，请使用具有以下语法的命令：

DROP DATABASE [database_name]
GO

例如：

DROP DATABASE sampledb;
GO

3.管理MSSQL Server 2022中的用户和权限

默认情况下，MSSQL 附带一个用户，即用户名为“sa”的 admin 用户。但是，可以创建其他用户并授予他们访问各种数据库的权限。

在MSSQL中，用户管理，有几个概念：

• 登录：需要登录才能连接到 MSSQL 服务器实例。它用于验证用户身份并授予访问数据库引擎的权限。可以使用 SQL Server Management Studio (SSMS) 或使用 T-SQL 命令 CREATE LOGIN 创建登录名。
• 用户：用户是与特定数据库关联的安全主体。它用于授予访问和修改数据库内数据的权限。可以使用 SSMS 或使用 T-SQL 命令 CREATE USER 创建用户
• 范围：登录名是存在于 master 数据库中的服务器级对象，可用于连接到服务器上的任何数据库。另一方面，用户是存在于特定数据库上下文中的数据库级对象。
• 身份验证：MSSQL 服务器使用 Windows 身份验证或 SQL Server 身份验证对登录进行身份验证。相反，数据库引擎使用登录凭据对用户进行身份验证。
• 权限：登录被授予服务器级权限，而用户被授予数据库级权限。这意味着登录可以访问服务器上的任何数据库，但用户只能访问与其关联的特定数据库。

A。在 MSSQL 上创建登录用户

如前所述，登录用户用于验证连接到数据库服务器的用户或应用程序。

要创建登录用户，请使用具有以下语法的命令：

CREATE LOGIN login_name WITH PASSWORD = 'password';
GO

例如;

CREATE LOGIN testuser WITH PASSWORD = 'Passw0rd!';
GO

创建完成后，您可以使用它登录MSSQL数据库服务器：

sqlcmd -S 127.0.0.1 -U testuser 

示例输出：

默认情况下，登录名没有访问任何数据库或数据库对象的任何权限。但是，登录名确实能够连接到数据库服务器，并且可以被授予服务器级权限来执行管理任务，例如创建数据库或管理服务器设置。要分配权限，请继续并向其标记用户。

要删除登录，请使用：

DROP LOGIN [username]
GO

要更改登录密码，请使用：

ALTER LOGIN [login_name] WITH PASSWORD = 'new_password';
GO

将 [login_name] 替换为您要更改密码的登录名，并将 'new_password' 替换为您要配置的新密码。

b.在 MSSQL 上创建用户

创建登录名后，该登录名必须与数据库用户关联，以便授予该用户对特定数据库和数据库对象的访问权限。如果没有此关联，登录将无法访问任何数据库。用户用于向登录名授予对特定数据库和数据库对象的访问权限。

现在以管理员用户身份登录，并将用户添加到之前创建的登录名中。该命令的语法是：

CREATE USER user_name FOR LOGIN login_name;

例如：

CREATE USER my_user FOR LOGIN testuser;
GO

要删除用户，请使用语法如下的命令：

DROP USER [username]
GO

要更改密码，请使用；

ALTER USER [userName] WITH PASSWORD = 'New Password'
GO

使用命令查看可用用户；

SELECT * FROM sys.database_principals WHERE Type = 'S'
GO

C。在 MSSQL 中向登录|用户授予权限

在MSSQL中，有几个内置的用户角色用于管理权限和对各种资源的访问。这些角色旨在简化向用户和组授予权限的过程。以下是 MSSQL 中一些常用的用户角色：

登录可以拥有服务器级权限和角色，例如“sysadmin”、“securityadmin”、“dbcreator” ”和“processadmin”，而用户拥有数据库级权限。可以授予用户访问数据库对象的权限，例如表、视图、存储过程和函数。可以向用户授予数据库级角色，例如“db_owner”、“db_datareader”、“db_datawriter”和“db_ddladmin” “

1. 对于登录：（要授予这些权限，请使用master数据库）
2. SysAdmin：此角色拥有对 MSSQL 实例的完全管理访问权限，并且可以在服务器上执行任何操作。
3. DbCreator：此角色可以创建、更改和删除数据库。
4. SecurityAdmin：此角色允许用户管理登录、用户和角色。
5. BackupOperator：此角色允许用户备份和恢复数据库。
6. BulkAdmin：此角色允许用户执行批量导入和导出操作。
7. DDLAdmin：此角色允许用户创建、更改和删除数据库对象，例如表、视图和存储过程。

8. 对于用户：
9. DbOwner：此角色拥有对特定数据库的完全管理访问权限，并且可以对该数据库执行任何操作。
10. DataReader：此角色允许用户读取数据库中所有用户表中的所有数据。
11. DataWriter：此角色允许用户插入、更新和删除数据库中所有用户表中的数据。

可以给创建的用户添加权限|登录才能授予权限，首先以admin用户连接MSSQL服务器；

sqlcmd -S 127.0.0.1 -U sa

我们可以使用sp_addrolemember命令来授予权限。您可以为用户添加上述任何角色。

• 授予数据库写入者角色：

USE [database_name]
GO
EXEC sp_addrolemember 'db_datawriter', 'username'
GO

• 授予数据库读取器角色：

USE [database_name]
GO
EXEC sp_addrolemember 'db_datareader', 'username'
GO

• 授予 dbcreator 角色：

USE master
GO
EXEC sp_addsrvrolemember 'dbcreator',  'loginname';
GO

• 授予系统管理员角色：

USE master
GO
EXEC sp_addsrvrolemember 'sysadmin', 'loginname'
GO

要使上述命令起作用，您需要将 database_name 替换为数据库的实际名称，将 username 替换为您实际使用的登录名称。想要将该角色授予。

为了演示它是否有效，我们可以向用户添加 DBCreator 角色：

USE sampledb
CREATE USER  my_user FOR LOGIN testuser;
GO
EXEC sp_addrolemember 'db_datareader', 'my_user';
GO

查看用户已添加的角色；

1> EXEC sp_helpuser 'my_user';
2> GO
UserName       RoleName     LoginName        DefDBName    DefSchemaName UserID     SID                                                                                                                                                                         
-------------- ------------ ---------------- ------------ ------------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
my_user        public       testuser         master       dbo           7          0x138EB720C61D394F96CBD43E42A800E9                                                                                                                                          
1> 

添加后，我们将测试是否能够创建和删除数据库：

d.撤销 MSSQL 上登录\用户的权限

要撤销用户的权限，请使用具有以下语法的命令：

REVOKE [role] FROM [user_name];
GO

例如：

REVOKE db_datawriter FROM [user_name];
GO

要撤销登录权限，请使用：

ALTER SERVER ROLE [role] DROP MEMBER [login];

例如：

ALTER SERVER ROLE dbcreator DROP MEMBER testuser;

判决

这标志着本关于如何在 MS SQL Server 2022 中管理数据库、用户和权限的指南的结束。我希望这能提供有用的信息。

在此页面上查看更多内容：

如何使用 Warpgate 设置 SSH 和 MySQL Bastion 服务器

在 Rocky Linux 8 上安装 Azure Data Studio|AlmaLinux 8

在 Docker/Podman 容器中运行 Microsoft SQL Server 2022