无法在dbo架构下创建表的SQL Server权限问题排查

环境信息 #

• Docker环境：Microsoft SQL Server 2022 (RTM-CU10) (KB5031778) - 16.0.4095.4 (X64)

操作流程 #

1. 创建数据库testdb，并创建登录名与用户：

use testdb
create login adarsh with password = 'table@123';
use testdb;
create user adarsh_user for login adarsh;

2. 授予创建架构权限：

use testdb;
grant create schema to adarsh_user;

3. 授予创建表权限：

use testdb;
grant CREATE TABLE to adarsh_user;

测试情况 #

• 指定自定义架构创建表成功：

use testdb;
create table [testdb].[adarsh_schema].[ads_1512_0536]( fam_member_id smallint, firstname text, lastname text);

• 使用默认架构创建表失败，错误提示：

指定的架构名称"dbo"不存在或您无权使用它。

当前权限与默认架构查询 #

数据库权限 #

默认架构 #

已尝试重新设置默认架构，但问题未解决：

ALTER USER adarsh_user WITH DEFAULT_SCHEMA = dbo;

解决方法 #

原因 #

用户默认架构为dbo，但未被授予在dbo架构下创建表的权限，也没有该架构的控制权限，导致不指定架构时无法在dbo下创建表。

方案1：授予dbo架构创建表权限 #

执行以下语句，给用户授权在dbo架构下创建表：

USE testdb;
GRANT CREATE TABLE ON SCHEMA::dbo TO adarsh_user;

方案2：授予dbo架构全面控制权限（按需选择） #

如果需要用户拥有dbo架构的更多操作权限，可执行：

USE testdb;
GRANT CONTROL ON SCHEMA::dbo TO adarsh_user;

方案3：设置用户专属默认架构 #

创建用户专属架构并设为默认，避免依赖dbo权限：

USE testdb;
CREATE SCHEMA adarsh_schema AUTHORIZATION adarsh_user;
ALTER USER adarsh_user WITH DEFAULT_SCHEMA = adarsh_schema;

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