无法创建EF Core迁移表：如何用T-SQL正确初始化SQL Server架构、数据库和用户？

问题复盘 #

执行EF Core首次迁移时触发错误：

Failed executing DbCommand (42ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
IF SCHEMA_ID(N'') IS NULL EXEC(N'CREATE SCHEMA [];');
CREATE TABLE [***].[Migrations] (
[MigrationId] nvarchar(150) NOT NULL,
[ProductVersion] nvarchar(32) NOT NULL,
CONSTRAINT [PK_Migrations] PRIMARY KEY ([MigrationId])
);

以sa身份执行授权语句时报错：

GRANT CREATE TABLE TO ***

Cannot find the user '***', because it does not exist or you do not have permission

后续更新：架构已在应用数据库中创建，但执行以下授权仍报错：

USE {Database}

GRANT CREATE TABLE TO {User}
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: [{Schema}] TO {User}

The specified schema name "{Schema}" either does not exist or you do not have permission to use it.

但可通过Azure Data Studio使用该用户访问数据库并查看架构。

问题根源 #

1. 初始化脚本逻辑错误：原脚本在master库中创建架构，而架构是数据库级对象，master库的架构与应用数据库无关，导致后续授权时无法识别目标架构。
2. 权限授予不完整：EF Core迁移需要的权限不止CRUD，还包括创建表、修改架构等权限。

解决方案 #

1. 修正数据库初始化脚本 #

将架构创建逻辑移至应用数据库脚本中，分两个脚本执行：

脚本1（创建数据库）：

USE master

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'$(Name)')
BEGIN
    CREATE DATABASE $(Name)
END

脚本2（创建登录、用户、架构）：

USE $(Name)

-- 在应用数据库中创建目标架构
IF NOT EXISTS (SELECT name FROM sys.schemas WHERE name = N'$(Schema)')
BEGIN
    EXEC sys.sp_executesql N'CREATE SCHEMA [$(Schema)] AUTHORIZATION [dbo]'
END

-- 创建服务器登录（如果不存在）
IF NOT EXISTS(SELECT principal_id FROM sys.server_principals WHERE name = '$(Login)') 
BEGIN
    CREATE LOGIN $(Login) 
    WITH PASSWORD = '$(Password)'
END

-- 创建数据库用户（如果不存在）并设置默认架构
IF NOT EXISTS(SELECT principal_id FROM sys.database_principals WHERE name = '$(User)') 
BEGIN
    CREATE USER $(User) FOR LOGIN $(Login)
    ALTER USER $(User) WITH DEFAULT_SCHEMA = [$(Schema)]
END

2. 授予完整迁移权限 #

在应用数据库下执行以下语句，为用户授予EF Core迁移所需的全部权限：

USE {Database}

-- 授予创建表权限
GRANT CREATE TABLE TO {User}
-- 授予目标架构的CRUD权限
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: [{Schema}] TO {User}
-- 授予修改架构权限（用于创建/修改架构内对象）
GRANT ALTER ON SCHEMA :: [{Schema}] TO {User}
-- 授予查看系统定义权限（迁移需读取系统元数据）
GRANT VIEW DEFINITION TO {User}

3. 验证权限 #

使用创建的登录连接数据库，执行以下语句验证权限是否生效：

-- 检查是否拥有CREATE TABLE权限
SELECT HAS_PERMS_BY_NAME(NULL, NULL, 'CREATE TABLE') AS HasCreateTablePermission
-- 检查对目标架构的权限
SELECT * FROM fn_my_permissions('[{Schema}]', 'SCHEMA')

执行完成后，重新运行EF Core迁移即可正常创建迁移表。

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