七、一键巡检脚本（SQL Server版）
-- ==========================================
-- SQL Server 等保三级一键巡检脚本
-- 执行方式：在SQL Server Management Studio (SSMS) 中执行
-- 权限要求：sysadmin 或 securityadmin + 各数据库访问权限
-- ==========================================
CREATETABLE#InspectionResults (
Category NVARCHAR(50),
Item NVARCHAR(100),
Status NVARCHAR(10),
Detail NVARCHAR(MAX),
CheckTime DATETIMEDEFAULT GETDATE()
);
DECLARE@Result NVARCHAR(MAX);
-- 1. 身份鉴别检查
PRINT'正在检查身份鉴别...';
-- 1.1 检查SQL认证和密码策略
INSERTINTO#InspectionResults (Category, Item, Status, Detail)
SELECT
'身份鉴别',
'密码策略启用',
CASE
WHENCOUNT(*)=0THEN'PASS'
WHENSUM(CASEWHEN is_policy_checked =0OR is_expiration_checked =0THEN1ELSE0END)=0THEN'PASS'
ELSE'WARN'
END,
'总登录数: '+ CAST(COUNT(*)AS NVARCHAR)+
', 未启用策略: '+ CAST(SUM(CASEWHEN is_policy_checked =0THEN1ELSE0END)AS NVARCHAR)+
', 未启用过期: '+ CAST(SUM(CASEWHEN is_expiration_checked =0THEN1ELSE0END)AS NVARCHAR)
FROM sys.sql_logins;
-- 1.2 检查sa账户
SELECT@Result=CASE
WHEN is_disabled =1THEN'PASS: sa账户已禁用'
WHEN is_policy_checked =1AND is_expiration_checked =1THEN'WARN: sa已启用但受密码策略保护'
ELSE'FAIL: sa账户处于活动状态且无密码策略'
END
FROM sys.sql_logins WHERE name ='sa';
INSERTINTO#InspectionResults VALUES ('身份鉴别', 'sa账户状态',
CASEWHEN@ResultLIKE'PASS%'THEN'PASS'WHEN@ResultLIKE'WARN%'THEN'WARN'ELSE'FAIL'END,
@Result);
-- 1.3 检查Windows认证优先
INSERTINTO#InspectionResults (Category, Item, Status, Detail)
SELECT
'身份鉴别',
'Windows认证',
CASEWHENCOUNT(*)>0THEN'PASS'ELSE'WARN'END,
'Windows认证登录数: '+ CAST(COUNT(*)AS NVARCHAR)
FROM sys.server_principals
WHEREtypeIN('U','G')AND is_disabled =0;
-- 2. 访问控制检查
PRINT'正在检查访问控制...';
-- 2.1 检查sysadmin权限分配
INSERTINTO#InspectionResults (Category, Item, Status, Detail)
SELECT
'访问控制',
'sysadmin权限审查',
CASEWHENCOUNT(*)<=2THEN'PASS'ELSE'WARN'END,
'具有sysadmin权限的登录数: '+ CAST(COUNT(*)AS NVARCHAR)+
' (建议仅保留1-2个管理员)'
FROM sys.server_role_members rm
JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.server_principals m ON rm.member_principal_id = m.principal_id
WHERE r.name ='sysadmin';
-- 2.2 检查guest用户
INSERTINTO#InspectionResults (Category, Item, Status, Detail)
SELECT
'访问控制',
'guest用户权限',
CASEWHENCOUNT(*)=0THEN'PASS'ELSE'FAIL'END,
CASE
WHENCOUNT(*)=0THEN'未发现guest用户直接授权'
ELSE'发现'+ CAST(COUNT(*)AS NVARCHAR)+'个数据库中guest用户有权限'
END
FROM sys.databases db
WHEREEXISTS(
SELECT1FROM sys.database_permissions p
JOIN sys.database_principals u ON p.grantee_principal_id = u.principal_id
WHERE u.name ='guest'AND p.major_id >0
);
-- 3. 安全审计检查
PRINT'正在检查安全审计...';
-- 3.1 检查服务器审计
INSERTINTO#InspectionResults (Category, Item, Status, Detail)
SELECT
'安全审计',
'服务器审计配置',
CASE
WHENCOUNT(*)=0THEN'FAIL'
WHENSUM(CASEWHEN is_state_enabled =1THEN1ELSE0END)=0THEN'WARN'
ELSE'PASS'
END,
'审计配置数: '+ CAST(COUNT(*)AS NVARCHAR)+
', 已启用: '+ CAST(SUM(CASEWHEN is_state_enabled =1THEN1ELSE0END)AS NVARCHAR)
FROM sys.server_audits;
-- 3.2 检查审计规范
INSERTINTO#InspectionResults (Category, Item, Status, Detail)
SELECT
'安全审计',
'审计规范配置',
CASE
WHENCOUNT(*)=0THEN'WARN'
WHENSUM(CASEWHEN is_state_enabled =1THEN1ELSE0END)=0THEN'WARN'
ELSE'PASS'
END,
'审计规范数: '+ CAST(COUNT(*)AS NVARCHAR)+
', 已启用: '+ CAST(SUM(CASEWHEN is_state_enabled =1THEN1ELSE0END)AS NVARCHAR)
FROM sys.server_audit_specifications;
-- 4. 入侵防范检查
PRINT'正在检查入侵防范...';
-- 4.1 检查xp_cmdshell
INSERTINTO#InspectionResults (Category, Item, Status, Detail)
SELECT
'入侵防范',
'xp_cmdshell状态',
CASEWHENvalue=0THEN'PASS'ELSE'FAIL'END,
'当前值: '+ CAST(valueAS NVARCHAR)+' (0=禁用, 1=启用)'
FROM sys.configurations WHERE name ='xp_cmdshell';
-- 4.2 检查OLE自动化
INSERTINTO#InspectionResults (Category, Item, Status, Detail)
SELECT
'入侵防范',
'OLE Automation',
CASEWHENvalue=0THEN'PASS'ELSE'WARN'END,
'当前值: '+ CAST(valueAS NVARCHAR)
FROM sys.configurations WHERE name ='Ole Automation Procedures';
-- 4.3 检查CLR
INSERTINTO#InspectionResults (Category, Item, Status, Detail)
SELECT
'入侵防范',
'CLR Enabled',
CASEWHENvalue=0THEN'PASS'ELSE'WARN'END,
'当前值: '+ CAST(valueAS NVARCHAR)
FROM sys.configurations WHERE name ='clr enabled';
-- 5. 数据安全检查
PRINT'正在检查数据安全...';
-- 5.1 检查TDE加密
INSERTINTO#InspectionResults (Category, Item, Status, Detail)
SELECT
'数据安全',
'透明数据加密(TDE)',
CASEWHENSUM(CASEWHEN is_encrypted =1THEN1ELSE0END)>0THEN'PASS'ELSE'WARN'END,
'加密数据库数: '+ CAST(SUM(CASEWHEN is_encrypted =1THEN1ELSE0END)AS NVARCHAR)+
'/'+ CAST(COUNT(*)AS NVARCHAR)+' (系统数据库除外)'
FROM sys.databasesWHERE name NOTIN('master','tempdb','model','msdb');
-- 5.2 检查连接加密
INSERTINTO#InspectionResults (Category, Item, Status, Detail)
SELECT
'数据安全',
'连接加密',
CASE
WHENCOUNT(*)=0THEN'WARN'
WHENSUM(CASEWHEN encrypt_option ='TRUE'THEN1ELSE0END)=COUNT(*)THEN'PASS'
ELSE'WARN'
END,
'总连接: '+ CAST(COUNT(*)AS NVARCHAR)+
', 加密: '+ CAST(SUM(CASEWHEN encrypt_option ='TRUE'THEN1ELSE0END)AS NVARCHAR)+
', 未加密: '+ CAST(SUM(CASEWHEN encrypt_option ='FALSE'THEN1ELSE0END)AS NVARCHAR)
FROM sys.dm_exec_connections WHERE net_transport ='TCP';
-- 6. 高可用检查
PRINT'正在检查高可用...';
-- 6.1 检查AlwaysOn
INSERTINTO#InspectionResults (Category, Item, Status, Detail)
SELECT
'高可用',
'AlwaysOn可用性组',
CASEWHENCOUNT(*)>0THEN'PASS'ELSE'WARN'END,
'可用性组副本数: '+ CAST(COUNT(*)AS NVARCHAR)
FROM sys.availability_replicas;
-- 6.2 检查备份策略
INSERTINTO#InspectionResults (Category, Item, Status, Detail)
SELECT
'高可用',
'备份策略',
CASE
WHENCOUNT(*)=0THEN'FAIL'
WHEN DATEDIFF(day,MAX(backup_finish_date), GETDATE())>7THEN'WARN'
ELSE'PASS'
END,
'最近备份: '+ ISNULL(CONVERT(NVARCHAR,MAX(backup_finish_date),120),'无')+
', 备份数据库数: '+ CAST(COUNT(DISTINCT database_name)AS NVARCHAR)
FROM msdb.dbo.backupset WHEREtype='D';
-- 输出结果
PRINT'';
PRINT'==========================================';
PRINT' SQL Server 等保三级巡检结果';
PRINT'==========================================';
SELECT
Category AS[检查类别],
Item AS[检查项],
StatusAS[状态],
Detail AS[详细信息],
CheckTime AS[检查时间]
FROM#InspectionResults
ORDERBY
CASE Category
WHEN'身份鉴别'THEN1
WHEN'访问控制'THEN2
WHEN'安全审计'THEN3
WHEN'入侵防范'THEN4
WHEN'数据安全'THEN5
WHEN'高可用'THEN6
ELSE7
END,
Item;
-- 统计
SELECT
Status,
COUNT(*)AS[数量]
FROM#InspectionResults
GROUPBYStatus
ORDERBYCASEStatusWHEN'PASS'THEN1WHEN'WARN'THEN2WHEN'FAIL'THEN3ELSE4END;
-- 清理
DROPTABLE#InspectionResults;
PRINT'';
PRINT'巡检完成。';
高风险项重点核查清单
SQL Server版本差异说明
常用命令速查
-- 连接与服务器信息
SELECT @@SERVERNAME, @@VERSION, DB_NAME();
EXEC sp_helpserver;
-- 用户管理
CREATE LOGIN userWITH PASSWORD ='StrongP@ssw0rd' MUST_CHANGE, CHECK_POLICY =ON;
ALTER LOGIN userDISABLE;
DROP LOGIN user;
-- 权限管理
GRANTSELECTONschema::dbo TOuser;
DENYDELETEONschema::dbo TOuser;
REVOKEINSERTONschema::dbo FROMuser;
-- 角色管理
EXEC sp_addrolemember 'db_datareader','user';
EXEC sp_droprolemember 'db_datawriter','user';
-- 审计管理
CREATE SERVER AUDIT audit TOFILE(PATH ='D:\Audit\');
CREATE SERVER AUDIT SPECIFICATION spec FOR SERVER AUDIT audit ADD (FAILED_LOGIN_GROUP);
ALTER SERVER AUDIT audit WITH (STATE = ON);
-- 加密管理
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256;
ALTER DATABASE db SET ENCRYPTION ON;
-- 备份恢复
BACKUP DATABASE db TO DISK = 'D:\Backup\db.bak' WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = cert);
RESTORE DATABASE db FROM DISK = 'D:\Backup\db.bak';
-- 性能监控
EXEC sp_who2;
SELECT * FROM sys.dm_exec_requests;
SELECT * FROM sys.dm_exec_sessions WHERE status = 'sleeping';