Azure SQL Server中WITH EXECUTE AS 'dbo'引发AD用户模拟错误求助

将Azure SQL Server数据库恢复到另一台服务器后，通过PowerShell的Invoke-Sqlcmd执行原本正常的存储过程sp_test时，出现错误：Only Active Directory users can impersonate other Active Directory users。该存储过程包含WITH EXECUTE AS 'dbo'语句，原服务器执行无问题，但担心移除该语句会有影响，现咨询：

1. WITH EXECUTE AS 'dbo'的作用
2. 报错原因
3. 解决方案

执行命令 #

Invoke-Sqlcmd -Query "exec dbo.sp_test 'Tom Jerry', 'test@test.com', 'fsf87901-gh12-400e-89nk-1f893cg923f1', 'Testing'" -ServerInstance database-server -Database test-db -Username test-user -Password test-password

存储过程定义 #

CREATE PROCEDURE [dbo].[sp_test]
    (@name varchar(100),
     @email varchar(200),
     @guid varchar(100),
     @campaign varchar(100))
WITH EXECUTE AS 'dbo'
AS
BEGIN
    SELECT @name, @email, @guid, @campaign
END
GO

错误信息 #

Invoke-Sqlcmd : Only Active Directory users can impersonate other Active Directory users.
At line:1 char:1

• Invoke-Sqlcmd -Query "exec dbo.sp_test 'Tom Jerry', ...
• CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
• FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

1. WITH EXECUTE AS 'dbo'的作用 #

• 指定存储过程执行时，以dbo用户的权限上下文运行，而非调用该存储过程的用户权限。
• 常见使用场景：
  • 调用者权限不足，但需要执行只有dbo才能操作的逻辑（比如修改系统表、执行高权限存储过程）；
  • 确保存储过程内的所有操作统一使用dbo权限，避免不同调用者权限不一致导致的执行结果差异。

2. 报错原因 #

• 你当前使用的是SQL身份验证用户（test-user，通过用户名密码登录）调用存储过程，但目标服务器上的dbo是Azure AD身份用户。
• Azure SQL有严格的身份模拟限制：只有Azure AD身份的用户，才能模拟其他Azure AD身份用户；SQL身份用户无法模拟Azure AD身份用户，这就是报错的核心原因。
• 原服务器能正常执行，要么是原服务器的dbo是SQL身份用户，要么你当时调用时使用的是Azure AD用户，符合模拟规则。

3. 解决方案 #

方案一：调整存储过程的执行上下文 #

如果存储过程的逻辑不需要依赖dbo的特殊权限（比如你的示例只是简单返回参数），可以移除WITH EXECUTE AS 'dbo'，或者显式指定WITH EXECUTE AS CALLER（这是默认行为，即使用调用者的权限执行）：

ALTER PROCEDURE [dbo].[sp_test]
    (@name varchar(100),
     @email varchar(200),
     @guid varchar(100),
     @campaign varchar(100))
-- 移除EXECUTE AS语句，默认使用调用者权限执行
AS
BEGIN
    SELECT @name, @email, @guid, @campaign
END
GO

注意：修改前务必确认存储过程是否依赖dbo的特殊权限（比如读写受限表、执行系统级操作），如果没有依赖，移除后不会产生影响。

方案二：改用Azure AD用户调用存储过程 #

如果必须保留WITH EXECUTE AS 'dbo'，则调用时使用Azure AD身份验证，而非SQL身份。修改PowerShell命令如下：

# 使用Azure AD集成身份（当前Windows登录用户为Azure AD用户）
Invoke-Sqlcmd -Query "exec dbo.sp_test 'Tom Jerry', 'test@test.com', 'fsf87901-gh12-400e-89nk-1f893cg923f1', 'Testing'" -ServerInstance database-server -Database test-db -Authentication ActiveDirectoryIntegrated

# 或者使用Azure AD用户名密码登录
Invoke-Sqlcmd -Query "exec dbo.sp_test 'Tom Jerry', 'test@test.com', 'fsf87901-gh12-400e-89nk-1f893cg923f1', 'Testing'" -ServerInstance database-server -Database test-db -Username your-aad-user@domain.com -Password your-aad-password -Authentication ActiveDirectoryPassword

方案三：修改目标服务器的dbo身份为SQL用户 #

如果目标服务器的权限体系允许，可以将数据库的dbo用户改为SQL身份用户（需要服务器管理员权限）：

1. 移除当前dbo映射的Azure AD账号；
2. 创建SQL身份用户，并将其设置为数据库的dbo。
   注意：此方案会影响整个数据库的权限体系，需谨慎操作，确认没有其他依赖Azure AD dbo的业务逻辑。

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