SQL Server新建用户可创建删除表却无法查看表的权限问题咨询

问题根因 #

该异常由两个权限配置错误导致：

• CREATE TABLE是数据库级权限，你使用的DENY CREATE TABLE TO roleExample AS dbo语法错误，AS dbo后缀不适用该场景，导致规则不生效
• 新建用户默认归属的public数据库角色被额外授予了两个必要权限：数据库级CREATE TABLE权限、所有Schema的ALTER权限。在SQL Server中创建/删除表需要同时满足「拥有数据库级CREATE TABLE权限」和「拥有目标Schema的ALTER权限」，两个条件同时满足才会出现你遇到的跨Schema建表现象
• 用户在SSMS中看不到自建表是因为没有VIEW DEFINITION权限，建表操作实际执行成功但无对象查看权限，所以SSMS对象资源管理器不展示

修复步骤 #

1. 清理public角色的多余权限 #

首先执行以下语句查询public角色的异常授权：

-- 查询public角色的数据库级权限
SELECT dp.permission_name, dp.state_desc
FROM sys.database_permissions dp
JOIN sys.database_principals dpri ON dp.grantee_principal_id = dpri.principal_id
WHERE dpri.name = 'public' AND dp.permission_name IN ('CREATE TABLE', 'ALTER ANY SCHEMA')

-- 查询public角色的Schema级ALTER权限
SELECT s.name AS schema_name, dp.permission_name, dp.state_desc
FROM sys.database_permissions dp
JOIN sys.database_principals dpri ON dp.grantee_principal_id = dpri.principal_id
JOIN sys.schemas s ON dp.major_id = s.schema_id
WHERE dpri.name = 'public' AND dp.permission_name = 'ALTER'

如果查询结果中存在GRANT或GRANT WITH GRANT类型的记录，执行对应语句回收：

-- 回收public角色的数据库级建表权限
REVOKE CREATE TABLE FROM public;
-- 回收public角色的全局Schema修改权限（如果存在）
REVOKE ALTER ANY SCHEMA FROM public;
-- 按查询结果回收单Schema的ALTER权限，示例回收dbo Schema的ALTER权限
REVOKE ALTER ON SCHEMA::dbo FROM public;

2. 修正自定义角色权限配置 #

替换你之前的DENY语句为如下写法，显式限制自定义角色的建表权限：

-- 先回收角色可能继承的建表权限
REVOKE CREATE TABLE FROM roleExample;
-- 可选：显式拒绝建表权限，避免其他角色继承带来的权限溢出
DENY CREATE TABLE TO roleExample;

如果需要让用户在SSMS中看到自己有权限操作的表，可按需授予查看定义权限：

-- 仅授予查看dbo.exampleTable的定义权限
GRANT VIEW DEFINITION ON dbo.exampleTable TO roleExample;
-- 可选：授予查看整个dbo Schema的对象定义权限
GRANT VIEW DEFINITION ON SCHEMA::dbo TO roleExample;

3. 权限验证 #

执行以下语句切换到测试用户，验证权限是否符合预期：

-- 切换到新建用户上下文
EXECUTE AS USER = 'userName';
-- 查看当前用户的数据库级有效权限，确认无CREATE TABLE权限
SELECT permission_name FROM fn_my_permissions(NULL, 'DATABASE') WHERE permission_name = 'CREATE TABLE';
-- 查看当前用户的dbo Schema权限，确认无ALTER权限
SELECT permission_name FROM fn_my_permissions('dbo', 'SCHEMA') WHERE permission_name = 'ALTER';
-- 切回原登录身份
REVERT;

验证通过后，测试用户执行建表/删表语句会直接返回权限不足报错，仅能对dbo.exampleTable执行增删改查操作，符合预期。

内容的提问来源于stack exchange，提问作者Cedric Jüssen