等保测评命令——SQL server

二、访问控制（8.1.4.2）

2.1 权限管理与角色分离

-- 13. 查看固定服务器角色及其成员
SELECT
r.name AS[角色名],
m.name AS[成员登录名],
r.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 m ON rm.member_principal_id = m.principal_id
ORDERBY r.name, m.name;

-- 14. 查看具有sysadmin权限的登录（最高权限，需严格审查）
SELECT
l.name AS[登录名],
l.type_desc AS[类型],
r.name AS[服务器角色],
'拥有完全控制权'AS[权限说明]
FROM sys.server_role_members rm
JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.server_principals l ON rm.member_principal_id = l.principal_id
WHERE r.name ='sysadmin'
ORDERBY l.name;

-- 15. 查看每个数据库的用户和权限
EXEC sp_MSforeachdb '
USE [?];
SELECT
DB_NAME() AS [数据库名],
u.name AS [用户名],
u.type_desc AS [类型],
r.name AS [数据库角色],
p.permission_name AS [权限],
p.state_desc AS [权限状态]
FROM sys.database_principals u
LEFT JOIN sys.database_role_members rm ON u.principal_id = rm.member_principal_id
LEFT JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
LEFT JOIN sys.database_permissions p ON u.principal_id = p.grantee_principal_id
WHERE u.type IN (''S'', ''U'', ''G'', ''R'')
ORDER BY u.name;
';

-- 16. 查看特定对象的权限分配（以表为例）
SELECT
o.name AS[对象名],
u.name AS[被授予者],
p.permission_name AS[权限],
p.state_desc AS[状态]
FROM sys.database_permissions p
JOIN sys.objects o ON p.major_id = o.object_id
JOIN sys.database_principals u ON p.grantee_principal_id = u.principal_id
WHERE p.class =1AND o.type='U'-- U=用户表
ORDERBY o.name, u.name;

-- 17. 检查直接授权（非角色授权，建议最小化）
SELECT
grantee.name AS[被授予者],
grantor.name AS[授权者],
p.permission_name,
p.state_desc,
o.name AS[对象名]
FROM sys.database_permissions p
JOIN sys.database_principals grantee ON p.grantee_principal_id = grantee.principal_id
JOIN sys.database_principals grantor ON p.grantor_principal_id = grantor.principal_id
LEFTJOIN sys.objects o ON p.major_id = o.object_id
WHERE grantee.typeIN('S','U')-- 直接用户授权
AND p.major_id >0-- 排除数据库级权限
ORDERBY grantee.name;

2.2 数据库级访问控制

-- 18. 查看所有数据库及所有者
SELECT
name AS[数据库名],
SUSER_SNAME(owner_sid)AS[所有者],
is_trustworthy_on AS[可信数据库],
is_db_chaining_on AS[跨库链],
is_broker_enabled AS[Service Broker],
is_encrypted AS[已加密],
create_date,
compatibility_level
FROM sys.databases
ORDERBY name;

-- 19. 检查guest用户权限（应禁用）
EXEC sp_MSforeachdb '
USE [?];
SELECT
DB_NAME() AS [数据库],
name,
permission_name,
state_desc
FROM sys.database_permissions p
JOIN sys.database_principals u ON p.grantee_principal_id = u.principal_id
WHERE u.name = ''guest'';
';

-- 20. 查看数据库角色及其成员
EXEC sp_helprole;
EXEC sp_helprolemember;

-- 21. 查看应用程序角色（特殊角色）
SELECT
name,
default_schema_name,
create_date,
modify_date,
authentication_type_desc
FROM sys.database_principals
WHEREtype='A';-- A=应用程序角色