SQL server

![](/media/202509/2025-09-12_205930_6655140.6977547675493877.png)

## 概述

SQL Server 是微软开发的**关系型数据库管理系统（RDBMS）**，专注于企业级应用，以强大的性能、安全性、集成性和易用性著称。

最初由 Microsoft、Sybase 和 Ashton-Tate 共同开发，首个 OS/2 版本于 1988 年推出。在  Windows NT 推出后，Microsoft 将 SQL Server 移植到 Windows NT 系统上，并专注于其 Windows  版本的开发。

官方网站：https://www.microsoft.com/zh-cn/sql-server（提供版本对比、下载、文档和技术资源）

**默认 TCP 端口**：1433（数据库引擎默认端口，可在安装或配置时修改）

其他相关端口：

- 命名实例动态端口（默认由 SQL Server 自动分配，可通过 SQL Server 配置管理器固定）
- SQL Server 浏览器服务：1434（UDP 端口，用于解析命名实例的端口）

## 适用场景

SQL Server 提供多个版本，满足不同需求：

- **SQL Server Express**：免费版，适合开发、小型应用（限制数据库大小最大 10GB）。
- **SQL Server Standard**：标准版，适合中小企业，支持基本高可用和性能功能。
- **SQL Server Enterprise**：企业版，适合大型企业，支持高级功能（如 AlwaysOn 可用性组、列存储索引、分区表等）。
- **Azure SQL**：云版本（PaaS），托管在 Azure 云平台，无需管理底层基础设施。

## 默认数据库

SQL Server 安装后自动创建 4 个核心系统数据库，用于管理数据库引擎的运行：

| 数据库名称 | 作用说明                                                     |
| ---------- | ------------------------------------------------------------ |
| master     | 核心系统数据库，存储所有数据库的元数据（如数据库列表、文件位置、登录名），删除会导致 SQL Server 无法启动。 |
| model      | 模板数据库，新建数据库时默认复制此数据库的结构（如默认配置、权限），修改 model 可统一新数据库的默认设置。 |
| msdb       | 用于 SQL Server 代理服务（作业调度、警报、备份历史），存储自动化任务相关数据。 |
| tempdb     | 临时数据库，存储临时表、游标、排序中间结果等，SQL Server 重启后自动重建（数据不持久化）。 |

## 核心特性

| 特性                 | 说明                                                         |
| -------------------- | ------------------------------------------------------------ |
| **完全 ACID 兼容**   | 严格支持事务的原子性、一致性、隔离性、持久性，确保关键业务数据可靠（如金融交易）。 |
| **高可用性**         | 提供多种高可用方案： - AlwaysOn 可用性组（多副本同步，自动故障转移）； - 数据库镜像（主从同步）； - 日志传送（异步备份与恢复）。 |
| **强大的安全功能**   | 支持透明数据加密（TDE）、行级安全（RLS）、动态数据屏蔽（DDM）、Azure AD 集成认证等，保护敏感数据。 |
| **性能优化**         | 内置查询优化器、列存储索引（适合数据仓库）、内存优化表（高并发场景）、自动统计信息更新等，提升查询效率。 |
| **集成开发工具**     | 与 Visual Studio、SQL Server Management Studio（SSMS）深度集成，支持图形化管理和 T-SQL 脚本开发。 |
| **商业智能（BI）**   | 内置 SQL Server Integration Services（SSIS，ETL 工具）、Analysis Services（SSAS，数据分析）、Reporting Services（SSRS，报表生成），形成完整 BI 解决方案。 |
| **跨平台支持**       | 自 SQL Server 2017 起支持 Linux 和 Docker 容器，打破传统 Windows 平台限制。 |
| **JSON 与 XML 支持** | 原生支持 JSON 数据的存储、查询和转换，以及 XML 数据类型和 XQuery 语法，兼容半结构化数据场景。 |

## 数据库操作

可以使用**SQL Server Management Studio（SSMS）**图形化管理工具 或者 **sqlcmd**（命令行工具）适合脚本自动化进行连接。

```bash
# sqlcmd 连接示例（默认实例）
sqlcmd -S localhost -U sa -P YourPassword

# 连接命名实例（如 localhost\SQLEXPRESS）
sqlcmd -S localhost\SQLEXPRESS -U sa -P YourPassword
```

**创建数据库**

SQL Server 数据库由**数据文件**（.mdf 主文件、.ndf 次要文件）和**日志文件**（.ldf）组成，创建时需指定文件路径和大小。

```sql
CREATE DATABASE 数据库名
ON PRIMARY (
  NAME = 数据文件逻辑名,
  FILENAME = '物理路径\文件名.mdf',  -- 如 'C:\SQLData\ShopDB.mdf'
  SIZE = 初始大小MB,
  MAXSIZE = 最大大小MB | UNLIMITED,
  FILEGROWTH = 增长幅度MB  -- 自动增长设置
)
LOG ON (
  NAME = 日志文件逻辑名,
  FILENAME = '物理路径\文件名.ldf',  -- 如 'C:\SQLLog\ShopDB.ldf'
  SIZE = 初始大小MB,
  MAXSIZE = 最大大小MB | UNLIMITED,
  FILEGROWTH = 增长幅度MB
);
```

```sql
CREATE DATABASE ShopDB
ON PRIMARY (
  NAME = ShopDB_Data,
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\ShopDB.mdf',
  SIZE = 10MB,
  MAXSIZE = UNLIMITED,
  FILEGROWTH = 5MB
)
LOG ON (
  NAME = ShopDB_Log,
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\ShopDB.ldf',
  SIZE = 5MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 10%  -- 按百分比增长
);
GO  -- SQL Server 批处理分隔符

```

**查看数据库**

```sql
-- 查看所有数据库
SELECT name, database_id, create_date 
FROM sys.databases;

-- 切换当前数据库
USE ShopDB;
GO
```

**修改数据库**

```sql
ALTER DATABASE 数据库名
  [MODIFY NAME = 新数据库名]  -- 重命名
  [ADD FILE (数据文件参数)]  -- 添加次要数据文件
  [MODIFY FILE (数据文件参数)]  -- 修改文件属性（如大小）
  [SET AUTO_SHRINK = ON | OFF]  -- 自动收缩（不推荐开启）
  [SET RECOVERY FULL | SIMPLE | BULK_LOGGED];  -- 设置恢复模式
```

```sql
-- 重命名数据库
ALTER DATABASE ShopDB MODIFY NAME = ECommerceDB;
GO

-- 增加次要数据文件（扩展存储空间）
ALTER DATABASE ECommerceDB
ADD FILE (
  NAME = ECommerceDB_Data2,
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\ShopDB2.ndf',
  SIZE = 10MB,
  FILEGROWTH = 5MB
);
GO
```

**删除数据库**

```sql
DROP DATABASE [IF EXISTS] 数据库名;
GO
```

```sql
-- 删除数据库（确保无连接）
DROP DATABASE IF EXISTS ECommerceDB;
GO
```

## 表操作

**创建表**

SQL Server 表结构定义需指定列名、数据类型和约束（主键、外键等），支持自增列（IDENTITY）。

```sql
CREATE TABLE 表名 (
  列名 数据类型 [约束],
  ...
  [表级约束]
);
```

创建 Users 表和 Orders 表（含外键关联）：
```sql
USE ShopDB;
GO

-- 创建用户表
CREATE TABLE Users (
  UserID INT IDENTITY(1,1) PRIMARY KEY,  -- 自增列（起始1，步长1），主键
  Username NVARCHAR(50) NOT NULL UNIQUE,  -- NVARCHAR 支持 Unicode（中文等）
  Email NVARCHAR(100) NOT NULL UNIQUE,
  Age INT CHECK (Age >= 0),  -- 年龄非负
  CreatedAt DATETIME DEFAULT GETDATE()  -- 默认当前时间（GETDATE() 为系统函数）
);
GO

-- 创建订单表（外键关联 Users.UserID）
CREATE TABLE Orders (
  OrderID INT IDENTITY(1,1) PRIMARY KEY,
  UserID INT NOT NULL,
  TotalAmount DECIMAL(10, 2) NOT NULL CHECK (TotalAmount >= 0),  -- 金额保留2位小数
  Status NVARCHAR(20) NOT NULL DEFAULT N'待付款',  -- N前缀表示Unicode字符串
  CreatedAt DATETIME DEFAULT GETDATE(),
  -- 外键约束：引用 Users 表的 UserID
  CONSTRAINT FK_Orders_Users FOREIGN KEY (UserID) 
    REFERENCES Users(UserID) 
    ON DELETE CASCADE  -- 级联删除：用户删除时，其订单也删除
);
GO
```

**查看表**

```sql
-- 查看当前数据库所有表
SELECT name AS TableName 
FROM sys.tables;

-- 查看表结构（列信息）
EXEC sp_help 'Users';  -- 系统存储过程

-- 或查询系统视图
SELECT 
  COLUMN_NAME, DATA_TYPE, IS_NULLABLE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'Users';
```

**修改表**

```sql
ALTER TABLE 表名
  [ADD 列名 数据类型 [约束]]  -- 添加列
  [ALTER COLUMN 列名 新数据类型]  -- 修改列类型
  [DROP COLUMN 列名]  -- 删除列
  [ADD CONSTRAINT 约束名 约束定义]  -- 添加约束
  [DROP CONSTRAINT 约束名];  -- 删除约束
```

```sql
-- 向 Users 表添加手机号列
ALTER TABLE Users 
ADD Phone NVARCHAR(20) UNIQUE;
GO

-- 修改 Users 表的 Age 列类型为 SMALLINT（节省空间）
ALTER TABLE Users 
ALTER COLUMN Age SMALLINT;
GO

-- 向 Orders 表添加索引（优化查询）
ALTER TABLE Orders 
ADD CONSTRAINT IX_Orders_UserID 
INDEX (UserID);
GO

-- 删除约束（如检查约束）
ALTER TABLE Users 
DROP CONSTRAINT CK__Users__Age__...;  -- 约束名可通过 sp_help 'Users' 查看
GO
```

**删除表**

```sql
DROP TABLE [IF EXISTS] 表名;
GO
```

```sql
-- 删除 Orders 表（若有外键依赖，需先删除外键或用 CASCADE）
DROP TABLE IF EXISTS Orders;
GO

-- 删除 Users 表
DROP TABLE IF EXISTS Users;
GO
```

## 数据操作

**插入数据**

```sql
INSERT INTO 表名 (列1, 列2, ...)
VALUES (值1, 值2, ...);

-- 批量插入
INSERT INTO 表名 (列1, 列2, ...)
VALUES (值1, 值2, ...), (值3, 值4, ...);
GO
```

```sql
-- 插入用户数据
INSERT INTO Users (Username, Email, Age)
VALUES 
  (N'张三', N'zhangsan@example.com', 25),
  (N'李四', N'lisi@example.com', 30);
GO

-- 插入订单数据
INSERT INTO Orders (UserID, TotalAmount, Status)
VALUES 
  (1, 99.99, N'已付款'),
  (1, 159.50, N'待付款'),
  (2, 299.00, N'已付款');
GO
```

**查询数据**

```sql
SELECT [TOP 数量] 列1, 列2, ...  -- TOP 限制返回行数（替代 LIMIT）
FROM 表名
[WHERE 条件]
[GROUP BY 列]
[HAVING 分组条件]
[ORDER BY 列 [ASC|DESC]];
GO
```

```sql
-- 基础查询：查询所有用户
SELECT * FROM Users;
GO

-- 条件查询：年龄 > 25 且邮箱含 'example.com' 的用户
SELECT Username, Age, Email 
FROM Users 
WHERE Age > 25 AND Email LIKE '%example.com%';
GO

-- TOP 与排序：取前2条最新创建的用户
SELECT TOP 2 Username, CreatedAt 
FROM Users 
ORDER BY CreatedAt DESC;
GO

-- 分组统计：按订单状态分组，计算总金额和订单数
SELECT 
  Status, 
  COUNT(*) AS OrderCount, 
  SUM(TotalAmount) AS TotalMoney 
FROM Orders 
GROUP BY Status 
HAVING SUM(TotalAmount) > 100;
GO

-- 连接查询：查询用户及其订单
SELECT 
  u.Username, 
  o.OrderID, 
  o.TotalAmount, 
  o.Status 
FROM Users u
INNER JOIN Orders o ON u.UserID = o.UserID;
GO
```

**更新数据**

```sql
UPDATE 表名
SET 列1 = 值1, 列2 = 值2, ...
[WHERE 条件];  -- 无 WHERE 则更新所有行（谨慎！）
GO
```

```sql
-- 更新用户年龄
UPDATE Users 
SET Age = 26 
WHERE Username = N'张三';
GO

-- 批量更新订单状态
UPDATE Orders 
SET Status = N'已取消', CreatedAt = GETDATE()
WHERE Status = N'待付款' AND CreatedAt < '2024-01-01';
GO
```

**删除数据**

```sql
DELETE FROM 表名
[WHERE 条件];  -- 无 WHERE 则删除所有行（谨慎！）
GO
```

```sql
-- 删除指定用户的待付款订单
DELETE FROM Orders 
WHERE UserID = 1 AND Status = N'待付款';
GO

-- 删除年龄 > 100 的用户
DELETE FROM Users 
WHERE Age > 100;
GO
```

## 特有功能

**临时表（会话级临时数据存储）**

SQL Server 支持本地临时表（#表名，仅当前会话可见）和全局临时表（##表名，所有会话可见）。

```sql
-- 创建本地临时表（会话结束后自动删除）
CREATE TABLE #TempOrders (
  OrderID INT,
  TotalAmount DECIMAL(10, 2)
);

-- 插入数据
INSERT INTO #TempOrders 
SELECT OrderID, TotalAmount FROM Orders WHERE Status = N'已付款';

-- 查询临时表
SELECT * FROM #TempOrders;
GO
```

**窗口函数（高级分组计算）**

与 PostgreSQL 类似，SQL Server 支持窗口函数，用于组内排序、排名等。

```sql
-- 按用户分组，计算订单金额排名
SELECT 
  UserID,
  OrderID,
  TotalAmount,
  ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY TotalAmount DESC) AS RankInUser
FROM Orders;
GO

-- 计算累计金额
SELECT 
  OrderID,
  TotalAmount,
  SUM(TotalAmount) OVER (ORDER BY CreatedAt) AS CumulativeTotal
FROM Orders;
GO
```

**JSON 数据操作**

SQL Server 支持 JSON 数据的存储和查询（通过 NVARCHAR(MAX) 类型和 JSON 函数）。

```sql
-- 创建含 JSON 字段的表
CREATE TABLE Products (
  ProductID INT PRIMARY KEY,
  Info NVARCHAR(MAX)  -- 存储 JSON 数据
);

-- 插入 JSON 数据
INSERT INTO Products (ProductID, Info)
VALUES (1, N'{"Name":"笔记本电脑","Price":5999,"Tags":["电子","办公"]}');
GO

-- 查询 JSON 字段（JSON_VALUE 提取值，JSON_QUERY 提取对象/数组）
SELECT 
  ProductID,
  JSON_VALUE(Info, '$.Name') AS ProductName,
  JSON_VALUE(Info, '$.Price') AS Price,
  JSON_QUERY(Info, '$.Tags') AS Tags
FROM Products;
GO

-- 条件查询（JSON 字段中的标签包含 "办公"）
SELECT ProductID 
FROM Products 
WHERE JSON_QUERY(Info, '$.Tags') LIKE '%办公%';
GO
```

## 用户与权限管理

**登录用户名**

登录名用于连接 SQL Server 实例，需映射到数据库用户才能访问具体数据库。

```sql
-- 创建 SQL Server 身份验证登录名
CREATE LOGIN User1 WITH PASSWORD = 'P@ssw0rd';
GO
```

**创建数据库用户（数据库级）**

```sql
-- 在 ShopDB 中创建用户，关联登录名 User1
USE ShopDB;
GO
CREATE USER User1 FOR LOGIN User1;
GO
```

**授权权限**

```sql
-- 授予 User1 对 Users 表的查询和插入权限
GRANT SELECT, INSERT ON Users TO User1;
GO

-- 授予 User1 对 Orders 表的所有权限
GRANT ALL ON Orders TO User1;
GO

-- 授予数据库级权限（如创建表）
GRANT CREATE TABLE TO User1;
GO
```

**撤销权限**

```sql
REVOKE DELETE ON Orders FROM User1;
GO
```

## 权限查询

SQL Server 的权限信息存储在系统视图中，按 “服务器级别” 和 “数据库级别” 分层管理，需通过对应的系统视图查询。

**服务器级权限查询**

服务器级权限针对登录名（Login） 和服务器角色，控制对 SQL Server 实例的管理操作（如创建数据库、配置服务器参数）。

核心系统视图

- sys.server_principals：存储服务器级安全主体（登录名、服务器角色）。
- sys.server_permissions：存储服务器级权限分配信息。
- sys.server_role_members：存储服务器角色的成员关系。

查询所有登录名及其基本信息：

```sql
SELECT 
    name AS 登录名,
    type_desc AS 类型,  -- SQL_LOGIN（SQL登录）、WINDOWS_LOGIN（Windows登录）等
    is_disabled AS 是否禁用,
    create_date AS 创建时间,
    default_database_name AS 默认数据库
FROM sys.server_principals
WHERE type IN ('S', 'U', 'G')  -- S:SQL登录, U:Windows用户, G:Windows组
ORDER BY type_desc, name;

```

查询登录名的服务器级权限：

```sql
SELECT 
    p.name AS 登录名,
    pe.permission_name AS 权限名称,
    pe.state_desc AS 权限状态  -- GRANT:授予, DENY:拒绝, REVOKE:回收
FROM sys.server_permissions pe
JOIN sys.server_principals p 
    ON pe.grantee_principal_id = p.principal_id
WHERE p.type IN ('S', 'U', 'G')  -- 过滤登录名（排除系统角色）
ORDER BY p.name, pe.permission_name;

```

常见服务器级权限：CONTROL SERVER（服务器控制权限）、CREATE DATABASE（创建数据库）、ALTER ANY LOGIN（修改任何登录名）等。

查询服务器角色及其成员：

```sql
-- 1. 查看所有服务器角色（含系统角色和自定义角色）
SELECT 
    name AS 服务器角色名,
    type_desc AS 角色类型,
    is_fixed_role AS 是否系统固定角色  -- 1:系统角色（如sysadmin）, 0:自定义角色
FROM sys.server_principals
WHERE type = 'R';  -- R:服务器角色

-- 2. 查看指定服务器角色的成员（如sysadmin角色）
SELECT 
    r.name AS 服务器角色名,
    p.name AS 成员登录名,
    p.type_desc AS 成员类型
FROM sys.server_role_members rm
JOIN sys.server_principals r 
    ON rm.role_principal_id = r.principal_id
JOIN sys.server_principals p 
    ON rm.member_principal_id = p.principal_id
WHERE r.name = 'sysadmin';  -- 替换为目标角色名

```

系统固定服务器角色（如 sysadmin、serveradmin、dbcreator）拥有预定义权限，其中 sysadmin 为最高权限（等效于操作系统管理员）。

**数据库级权限查询（用户与数据库角色）**

数据库级权限针对数据库用户（User） 和数据库角色，控制对特定数据库的操作（如创建表、查询数据）。

核心系统视图：

- sys.database_principals：存储数据库级安全主体（用户、数据库角色）。
- sys.database_permissions：存储数据库级权限分配信息。
- sys.database_role_members：存储数据库角色的成员关系。
- sys.objects：存储数据库对象（表、视图等），用于关联对象级权限。

查询数据库用户及其对应登录名：

```sql
USE 目标数据库名;  -- 切换到目标数据库
GO

SELECT 
    dp.name AS 数据库用户名,
    dp.type_desc AS 用户类型,
    sp.name AS 关联登录名,  -- 数据库用户关联的服务器登录名
    dp.default_schema_name AS 默认架构
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp 
    ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U', 'G')  -- S:SQL用户, U:Windows用户, G:Windows组
ORDER BY dp.name;

```

查询数据库用户的数据库级权限：

```sql
USE 目标数据库名;
GO

SELECT 
    dp.name AS 数据库用户名,
    pe.permission_name AS 权限名称,
    pe.state_desc AS 权限状态,
    CASE 
        WHEN pe.class = 0 THEN '数据库级'
        WHEN pe.class = 1 THEN '对象级'  -- 表、视图等
        WHEN pe.class = 4 THEN '列级'
        ELSE '其他'
    END AS 权限级别
FROM sys.database_permissions pe
JOIN sys.database_principals dp 
    ON pe.grantee_principal_id = dp.principal_id
WHERE dp.type IN ('S', 'U', 'G')
ORDER BY dp.name, 权限级别;

```

常见数据库级权限：CREATE TABLE（创建表）、ALTER ANY USER（修改任何用户）、SELECT（查询权限，对象级时需指定表）等。

查询表 / 视图的对象级权限：
```sql
USE 目标数据库名;
GO

SELECT 
    dp.name AS 数据库用户名,
    o.name AS 对象名,  -- 表或视图名
    o.type_desc AS 对象类型,
    pe.permission_name AS 权限名称,
    pe.state_desc AS 权限状态
FROM sys.database_permissions pe
JOIN sys.database_principals dp 
    ON pe.grantee_principal_id = dp.principal_id
JOIN sys.objects o 
    ON pe.major_id = o.object_id
WHERE 
    o.type IN ('U', 'V')  -- U:用户表, V:视图
    AND dp.type IN ('S', 'U', 'G')
ORDER BY o.name, dp.name;

```

查询数据库角色及其成员：
```sql
USE 目标数据库名;
GO

-- 1. 查看所有数据库角色（含系统角色和自定义角色）
SELECT 
    name AS 数据库角色名,
    type_desc AS 角色类型,
    is_fixed_role AS 是否系统固定角色  -- 1:系统角色（如db_owner）, 0:自定义角色
FROM sys.database_principals
WHERE type = 'R';  -- R:数据库角色

-- 2. 查看指定数据库角色的成员（如db_owner角色）
SELECT 
    r.name AS 数据库角色名,
    m.name AS 成员用户名
FROM sys.database_role_members rm
JOIN sys.database_principals r 
    ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals m 
    ON rm.member_principal_id = m.principal_id
WHERE r.name = 'db_owner';  -- 替换为目标角色名

```

系统固定数据库角色（如 db_owner、db_datareader、db_datawriter）是常用角色，其中 db_owner 拥有数据库的全部权限。

## 审计机制

SQL Server 提供两种核心审计方式：**SQL Server Audit**（基于策略的审计，适合合规场景）和**扩展事件（Extended Events）**（轻量级追踪，适合性能敏感场景）。

**SQL Server Audit（企业级审计）**

SQL Server Audit 从 2008 版本引入，支持对服务器级和数据库级事件进行集中审计，日志可存储在文件、Windows 事件日志或应用程序日志中，满足合规性要求（如 GDPR、SOX）。

创建服务器审计（定义日志存储）：
```sql
-- 创建服务器审计（日志存储到文件）
CREATE SERVER AUDIT [ServerAudit]
TO FILE (
    FILEPATH = 'C:\SQLAuditLogs\'  -- 日志文件路径（需SQL Server服务账户有写入权限）
    ,MAXSIZE = 100 MB  -- 单个文件最大大小
    ,MAX_ROLLOVER_FILES = 10  -- 最大滚动文件数
)
WITH (
    QUEUE_DELAY = 1000  -- 事件延迟（毫秒）
    ,ON_FAILURE = CONTINUE  -- 审计失败时继续数据库操作（可选SHUTDOWN）
);
GO
```

创建服务器审计规范（追踪服务器级事件）：

```sql
-- 创建服务器审计规范
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpec]
FOR SERVER AUDIT [ServerAudit]
ADD (LOGIN_SUCCESSFUL_GROUP),  -- 成功登录事件
ADD (LOGIN_FAILED_GROUP),      -- 失败登录事件
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),  -- 服务器主体（登录名、角色）变更
ADD (SERVER_PERMISSION_CHANGE_GROUP);  -- 服务器权限变更
GO

```

服务器审计规范关联服务器审计，定义需要追踪的服务器级事件（如登录、服务器权限变更）。

创建数据库审计规范（追踪数据库级事件）：
```sql
USE 目标数据库名;
GO

-- 创建数据库审计规范
CREATE DATABASE AUDIT SPECIFICATION [DB_AuditSpec]
FOR SERVER AUDIT [ServerAudit]
ADD (SELECT ON OBJECT::dbo.Users BY public),  -- 追踪对dbo.Users表的查询
ADD (UPDATE ON OBJECT::dbo.Orders BY public),  -- 追踪对dbo.Orders表的更新
ADD (DATABASE_PERMISSION_CHANGE_GROUP),  -- 数据库权限变更
ADD (SCHEMA_OBJECT_CHANGE_GROUP);  -- 架构对象（表、视图）变更
GO
```

数据库审计规范针对特定数据库，定义需要追踪的数据库级事件（如数据修改、对象创建）。

启用审计：

```sql
-- 启用服务器审计
ALTER SERVER AUDIT [ServerAudit] WITH (STATE = ON);
-- 启用服务器审计规范
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpec] WITH (STATE = ON);
-- 启用数据库审计规范
USE 目标数据库名;
ALTER DATABASE AUDIT SPECIFICATION [DB_AuditSpec] WITH (STATE = ON);
GO
```

查看审计日志：
```sql
-- 查看审计日志（需VIEW SERVER STATE权限）
SELECT 
    event_time AS 事件时间,
    action_id AS 操作ID,
    succeeded AS 是否成功,
    session_server_principal_name AS 操作登录名,
    database_name AS 数据库名,
    object_name AS 操作对象,
    statement AS 执行的SQL语句
FROM sys.fn_get_audit_file('C:\SQLAuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC;
```

也可通过 SSMS 图形界面查看：展开 “安全性”→“审计”→右键审计名称→“查看审计日志”。

**扩展事件（Extended Events）**

扩展事件是轻量级的事件追踪机制，性能开销远低于 SQL Server Audit，适合实时监控和性能敏感场景（如高频交易系统）。

常用场景示例：追踪敏感表的修改。

```sql
-- 创建扩展事件会话（追踪对dbo.Users表的UPDATE操作）
CREATE EVENT SESSION [TrackUserUpdates] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(
    WHERE 
        database_name = N'目标数据库名' 
        AND schema_name = N'dbo' 
        AND object_name = N'Users' 
        AND statement LIKE N'%UPDATE%'
)
ADD TARGET package0.event_file(SET filename = N'C:\XEvents\TrackUserUpdates.xel')
WITH (STARTUP_STATE = OFF);  -- 不随服务器启动
GO

-- 启动会话
ALTER EVENT SESSION [TrackUserUpdates] ON SERVER STATE = START;

-- 查看事件数据
SELECT 
    event_data.value('(event/@name)[1]', 'varchar(50)') AS 事件名,
    event_data.value('(event/data[@name="statement"]/value)[1]', 'nvarchar(max)') AS SQL语句,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint')/1000 AS 持续时间(微秒),
    event_data.value('(event/action[@name="session_server_principal_name"]/value)[1]', 'nvarchar(100)') AS 执行登录名
FROM (
    SELECT CAST(event_data AS xml) AS event_data
    FROM sys.fn_xe_file_target_read_file('C:\XEvents\TrackUserUpdates*.xel', NULL, NULL, NULL)
) AS x;

```