SQL Server 2019创建仅单库SELECT权限用户失败求助

核心问题根源 #

你的问题本质是用户继承了超出预期的冗余权限，大概率由以下场景导致：

• 登录名被误授予服务器级高权限（如sysadmin、securityadmin）
• 数据库用户被加入db_owner、db_datawriter等高权限数据库角色
• 服务器public角色默认权限未限制，或其他数据库启用了guest用户

完整最小权限配置步骤 #

请严格按以下流程重新创建受限用户，确保权限精准可控：

1. 创建无额外权限的服务器登录名 #

-- 创建4个独立登录名，替换密码和用户名为实际值
CREATE LOGIN [restricted_user1] WITH PASSWORD = 'YourStrongPwd_123', CHECK_EXPIRATION = ON, CHECK_POLICY = ON;
CREATE LOGIN [restricted_user2] WITH PASSWORD = 'YourStrongPwd_123', CHECK_EXPIRATION = ON, CHECK_POLICY = ON;
CREATE LOGIN [restricted_user3] WITH PASSWORD = 'YourStrongPwd_123', CHECK_EXPIRATION = ON, CHECK_POLICY = ON;
CREATE LOGIN [restricted_user4] WITH PASSWORD = 'YourStrongPwd_123', CHECK_EXPIRATION = ON, CHECK_POLICY = ON;

注意：不要给这些登录名添加任何服务器角色，仅保留默认的public角色。

2. 映射到目标数据库并创建数据库用户 #

假设目标数据库为TargetDB，需授权的表为TargetTable：

USE TargetDB;
GO
-- 创建对应数据库用户，仅关联登录名，不加入任何角色
CREATE USER [restricted_user1] FOR LOGIN [restricted_user1];
CREATE USER [restricted_user2] FOR LOGIN [restricted_user2];
CREATE USER [restricted_user3] FOR LOGIN [restricted_user3];
CREATE USER [restricted_user4] FOR LOGIN [restricted_user4];
GO

3. 仅授予单表SELECT权限 #

USE TargetDB;
GO
-- 精准授予指定表的SELECT权限，无其他冗余权限
GRANT SELECT ON [TargetTable] TO [restricted_user1];
GRANT SELECT ON [TargetTable] TO [restricted_user2];
GRANT SELECT ON [TargetTable] TO [restricted_user3];
GRANT SELECT ON [TargetTable] TO [restricted_user4];
GO

4. 限制服务器级冗余权限 #

默认public角色拥有VIEW ANY DATABASE权限，会让用户看到所有数据库名称（但无法访问）。若需隐藏其他数据库，可撤销该权限：

REVOKE VIEW ANY DATABASE TO public;

5. 禁用其他数据库的guest用户 #

若其他数据库启用guest用户，未映射的登录名会通过该用户访问数据库，执行以下语句批量禁用：

-- 遍历非目标数据库禁用guest用户
DECLARE @DBName NVARCHAR(128);
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'TargetDB');

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('USE ' + QUOTENAME(@DBName) + '; DENY CONNECT TO guest;');
    FETCH NEXT FROM db_cursor INTO @DBName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;
GO

6. 权限验证 #

用任意受限用户登录SSMS，执行以下语句验证权限边界：

-- 预期成功：访问目标表
SELECT * FROM TargetDB.dbo.TargetTable;
-- 预期失败：修改目标表
UPDATE TargetDB.dbo.TargetTable SET ColumnName = 'test';
-- 预期失败：访问其他数据库
SELECT * FROM AnotherDB.dbo.AnotherTable;
-- 预期失败：切换到其他数据库查询对象
USE AnotherDB;
SELECT * FROM SYSOBJECTS WHERE xtype = 'U';

额外排查点 #

若仍存在权限溢出，执行以下语句排查：

• 查看登录名服务器权限：sp_helplogins 'restricted_user1'
• 查看数据库用户角色成员：USE TargetDB; sp_helprolemember 'restricted_user1'
• 查看用户具体权限：USE TargetDB; sp_helprotect @username='restricted_user1'

内容的提问来源于stack exchange，提问作者Joe Maracic