MSSQL 命令行操作说明 sql server 2022 命令行下进行配置管理

lggirls

已于 2024-04-21 09:22:52 修改

阅读量1.5k

点赞数 10

文章标签： sqlserver 数据库服务器

于 2024-04-17 23:04:41 首次发布

本文链接：https://blog.csdn.net/lggirls/article/details/137886663

版权

说明：本文的内容是因为我在导入Access2019的 *.accdb 格式的数据时，总是出错的背景下，不得已搜索和整理了一下，如何用命令行进行sql server 数据库和用户管理的方法，作为从Access2019 直接导出数据到sql server 数据库，再进行进一步操作的基础。

一、sql server 2022的安装

参考官网给出的方法即可，这里将命令搬过来，运行环境为 Debian12.5，root用户权限

curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | gpg --dearmor -o /usr/share/keyrings/microsoft-prod.gpg

curl -fsSL https://packages.microsoft.com/config/ubuntu/22.04/mssql-server-2022.list | tee /etc/apt/sources.list.d/mssql-server-2022.list

apt-get update
sudo apt-get install -y mssql-server

## 配置
/opt/mssql/bin/mssql-conf setup

注意：选择版本的时候用3 即可

二、命令行工具的安装

这里介绍的是 Win11 安装的方法

2.1 以管理员运行 cmd命令行

PS C:\Users\UserA> winget install sqlcmd

如果安装不成功，会提示下载地址不可用，把地址复制下来，独立下载，再双击安装即可

2.2 安装odbc驱动

直接用 Bing 搜索 ODBC Driver for SQL Server

同样是 *.msi 文件，双击安装即可

2.3 sqlcmd 环境变量的设置

安装完成后，在命令行无法直接运行，需要对 sqlcmd.exe 进行环境变量的配置。相关教程很多。直接搜索 Win11 环境变量配置，有很多教程。

地址： C:\Program Files\SqlCmd\sqlcmd.exe

三、用sqlcmd命令进入sql server 2022 并查看基本信息

3.1 命令行登录数据库服务器

PS C:\Users\UserA> sqlcmd -S 192.168.1.80 -U SA

3.2 查看当前的服务器上有那些数据库

1> SELECT Name from sys.databases;
2> go

四、新建数据库和表，向表中输入内容

4.1 新建数据库

1> CREATE DATABASE 个人信息库;
2> go

4.2 向数据库添加表

1> use 个人信息库;
2> CREATE TABLE 基本信息表 ( 编号 INT PRIMARY KEY, 姓名 VARCHAR(255) NOT NULL, 年龄 INT );
3> go
Changed database context to '个人信息库'.

4.3 向数据库中的表内添加数据

1> INSERT INTO 基本信息表 (编号, 姓名, 年龄)
2> VALUES (1, '张三', 25)
3> go
(1 row affected)
1> INSERT INTO 基本信息表 (编号, 姓名, 年龄)
2> VALUES (2, '李四', 30)
3> go
(1 row affected)

4.4 删除数据库和删除数据库中的表

## 先确保不再有任何连接到数据库
ALTER DATABASE 个人信息库 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
## 然后删除数据库
DROP DATABASE 个人信息库;
GO

## 删除库中的表
USE 个人信息库
DROP TABLE 基本信息表
GO

五、查看某一数据库内包含哪些表，表的表头是什么

5.1 查看一个数据库中的表

1> use 个人信息库
2> go
Changed database context to '个人信息库'.
1> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';
2> go
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
基本信息表

(1 row affected)
1>

5.2 查看表的字段有哪些，以及具体位置的数值，修改特定位置的数值

## 查看 基本信息表 有哪些字段
1> SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '基本信息表';
2> go
COLUMN_NAME
--------------------------------------------------------------------------------------------------------------------------------
编号
姓名
年龄

(3 rows affected)


## 知道了有哪些字段，查看 姓名 这一列的所有数值
1>USE 个人信息库;
2>SELECT 姓名 FROM 基本信息表;
3>GO

## 知道了具体的 姓名 例如 李四，查看 李四 这一行的全部信息
1>USE 个人信息库;
2>SELECT * FROM 基本信息表 WHERE 姓名 = '李四';
3>GO

## 查看 李四 的 年龄 字段的 数据
1>USE 个人信息库;
2>SELECT 年龄 FROM 基本信息表 WHERE 姓名 = '李四';
3>GO

## 修改 李四 的 年龄 字段的 数据
USE database_name;
UPDATE 基本信息表 SET 年龄 = 27 WHERE 姓名 = '李四';


## 注意：因为姓名的数据是 字符串，所以用 '' ,如果要查的字段是数字，则不需要！！！

注意：没有为数据库设置文字编码，可能会无法显示中文字段名称，需要进行配置

5.3 查看数据库的默认字符编码

## 查看服务器的默认语言
SELECT @@language

## 查看数据库的默认语言
USE 个人信息库
go
SELECT DATABASEPROPERTYEX(DB_NAME(), 'LCID') AS DefaultLanguageLCID;
go

## LCID 1033 是英文，简体中文是2052

## 更改数据库的默认语言
USE 个人信息库
go
ALTER DATABASE 个人信息库 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE 个人信息库 COLLATE Chinese_PRC_CI_AS;
ALTER DATABASE 个人信息库 SET MULTI_USER;

## 查看默认字符编码
1> SELECT SERVERPROPERTY('Collation');
2> go

## 显示的最后一行
SQL_Latin1_General_CP1_CI_AS

5.4 设置一个数据库的文字编码

1>ALTER DATABASE 个人信息库 COLLATE Chinese_PRC_CI_AS 
2>go

## 设置数据库内数据表的字符编码格式
USE 个人信息库
ALTER TABLE 基本信息表 COLLATE Chinese_PRC_CI_AS
GO

## 设置数据库内数据表的某个字段数值的字符编码格式
USE 个人信息库
ALTER TABLE 基本信息表 
ALTER COLUMN 姓名 COLLATE Chinese_PRC_CS_AI_WS
GO

5.5 再次查看“基本信息表”中的字段

六、新建用户和删除用户

sql server中的用户有两种：1. 登录用户(Login user)；2.一般用户(user)

6.0 查看当前系统上的用户名

1>USE master;
2>SELECT name FROM sys.sql_logins;
3>GO

6.1 创建登录用户

1>CREATE LOGIN AdminUserB WITH PASSWORD = 'Passw0rd!';
2>GO

6.2 更改密码

1>ALTER LOGIN AdminUserB WITH PASSWORD = 'new_password';
2>GO

注意：一般新建立的登录用户，没有权限对数据库进行操作，而是需要为其配置一般用户，并赋予一个数据库相关的权限后，才能进行数据库操作。也就是说，登录用户是管理 sql server 本身的，一般用户，是对应着数据库的。

6.3 为登录用户创建一般用户

CREATE USER UserB FOR LOGIN AdminUserB;
GO

注意：当前是以sa用户登录的，且没有进入任何的具体数据库，所以生成的用户 UserB 是针对当前环境的；如果要为AdminUserB 配置可以管理个人信息库这个数据库的用户，需要先 “user 个人信息库” 进入数据库，再创建一般用户。

6.4 查看一般用户有哪些仅能查看当前所在数据库的

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

6.5 更改一般用户的密码

ALTER USER UserB WITH PASSWORD = 'New Password'
GO

注意：这里是更改不了的，因为这个一般用户是数据库内映射登录用户 AdminUserB用的，和登录用户相关联，无法设置密码。要想创建不登录服务器，仅仅登录数据库的一般用户，需要开启服务和数据库的半包含的数据库功能。可查看第九部分。

6.6 用户的删除

1>DROP LOGIN AdminUserB
2>DROP USER UserB
3>GO

七、用户权限的配置

7.0 基本权限介绍

sql server 通过一些内置的角色，来给用户配置权限。对于Login，也就是登录用户，可以拥有一些服务器级别的权限角色，比如：“sysadmin“, “securityadmin“, “dbcreator“, “processadmin”。可以授予用户访问数据库对象（如表、视图、存储过程和函数）的权限，也就是向用户授予数据库级的角色，例如“db_owner”、“db_datareader”、“db_datawriter”、“db_ddladmin”。

7.0.1 看一下都有哪些固定的服务器角色

EXEC sp_helpsrvrole
GO

7.0.2 看一下当前login用户的服务器角色列表

EXEC sp_helpsrvrolemember
GO

7.0.3 数据库级别的角色权限

db_owner 权限最大；db_datawriter，db_datareader 最常用；db_accessadmin可以管理一个用户名是否可以登录数据库

7.0.4

7.0.5 dbmanager 角色的特殊性

首先这是一个为存在于虚拟master 数据库中记录的用户添加进该角色列表中；

其次，虚拟Master 中记录的login用户无法直接添加进去，需要创建对应的一般用户，然后添加进该角色列表；

7.1 登录用户的权限（需要进入 master 数据库，然后进行配置）

SysAdmin：此角色对 MSSQL 实例具有完全管理访问权限，可以在服务器上执行任何操作。
DbCreator：此角色可以创建、更改和删除数据库。
SecurityAdmin：此角色允许用户管理登录名、用户和角色。
BackupOperator：此角色允许用户备份和还原数据库。
BulkAdmin：此角色允许用户执行批量导入和导出操作。
DDLAdmin：此角色允许用户创建、更改和删除数据库对象，例如表、视图和存储过程。

7.2 一般用户的常用权限

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

注意：上述角色权限的配置要使用 sp_addrolemember 命令来完成！！

7.3 登录用户的角色权限配置示例

## 1.配置数据库的创建角色
1>USE master
2>GO
1>EXEC sp_addsrvrolemember 'dbcreator',  'AdminUserB';
2>GO

## 2.配置服务器管理员角色
1>USE master
2>GO
1>EXEC sp_addsrvrolemember 'sysadmin', 'AdminUserB';
2>GO

7.4 一般用户的角色权限配置示例

## 配置针对特定数据库的数据写入角色权限
USE 个人信息库
GO
EXEC sp_addrolemember 'db_datawriter', 'UserB'
GO

## 配置针对特定数据库的读取角色权限
USE 个人信息库
GO
EXEC sp_addrolemember 'db_datareader', 'UserB'
GO

7.5 查看某一用户的角色权限

1> EXEC sp_helpuser 'UserB';
2> GO

八、用户角色权限的取消

8.1 删除login 用户的角色权限

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

ALTER SERVER ROLE sysadmin DROP MEMBER AdminUserB;
GO

8.2 删除一般用户的角色权限

REVOKE db_datawriter FROM UserB;
GO

九、开启服务和数据库的包含的数据库功能

9.1 概念介绍

sql server 中的数据库被分为了3类：1.非包含的数据库，需要登录用户和映射到数据库内的一般用户来进行操作；2.半包含的数据库，有部分数据存储在 master 等系统数据库内，可以创建独立的一般用户；3.包含的数据库，所有信息独立。

举个例子， sql server 相当于 Debian12 操作系统，非包含的数据库，相当于直接安装在操作系统内的软件；半包含的数据库，相当于Docker 容器，需要用到操作系统的一部分内容；包含的数据，相当于 KVM虚拟机，是一个完全的独立系统。

9.2 开启半包含的数据库功能

并非是不想创建完全包含的数据库，而是暂时没有找到如何实施。官方只介绍和推荐半包含的数据库，说了一大堆好处。

9.2.1 为数据库服务器开启包含的数据库功能

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'contained database authentication', 1;  
GO  
RECONFIGURE;  
GO

注：设置为0 则是关闭

9.2.2 开启某个数据库的部分包含数据库功能

## 先进入 master 数据库
USE master;
GO
 
## 修改你的数据库设置为部分包含：
ALTER DATABASE 个人信息库 SET CONTAINMENT = PARTIAL;
GO
 
## 查看数据库的包含状态以确认更改：
SELECT name, containment_desc FROM sys.databases WHERE name = '个人信息库';
GO
 
## 或者
SELECT name, containment_desc FROM sys.databases;
GO

9.2.3 创建一般用户

CREATE USER UserC WITH PASSWORD = 'Super007!';
# 修改密码 
ALTER USER UserC WITH PASSWORD = 'Super008!';

9.2.4 用一般用户登录具体的某一数据库

sqlcmd -S 192.168.1.80 -d 个人信息库 -U UserC -P Super008!

这样登录后，仅能执行在 “个人信息库”中所具有的权限。