Azure Elastic Job定时执行失败，手动触发正常的权限问题求助

使用Azure弹性作业代理（Elastic Job Agent）在周末运行存储过程，对SQL Azure数据库进行维护。通过sp_start_job手动触发作业可成功执行，但定时调度时出现以下权限错误：

Microsoft.Data.SqlClient.SqlException: Msg 297, The user does not have permission to perform this action.

完整堆栈跟踪：

System.AggregateException: One or more errors occurred. ---> Microsoft.Azure.SqlDatabase.Jobs.Core.UserException: Command failed: Msg 297, The user does not have permission to perform this action.
    
Date and time: 2023-10-22 04:45:55
     ---> Microsoft.Data.SqlClient.SqlException: Msg 297, The user does not have permission to perform this action.
    
Date and time: 2023-10-22 04:45:55
    
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 2363
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParser.cs:line 1835
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParser.cs:line 0
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData() in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlDataReader.cs:line 1349
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData() in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlDataReader.cs:line 281
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 5787
   at Microsoft.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 5665
   at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, String endMethod, Boolean isInternal) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 2927
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 2602
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 2567
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.Azure.SqlDatabase.Jobs.Core.ScriptExecutionTaskExecutor.<ExecuteBatchAsync>d__6.MoveNext()
   --- End of inner exception stack trace ---
   at Microsoft.Azure.SqlDatabase.Jobs.Core.ScriptExecutionTaskExecutor.<ExecuteBatchAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.Azure.SqlDatabase.Jobs.Core.ScriptExecutionTaskExecutor.<ExecuteAsync>d__1.MoveNext()
   --- End of inner exception stack trace ---
---> (Inner Exception #0) Microsoft.Azure.SqlDatabase.Jobs.Core.UserException: Command failed: Msg 297, The user does not have permission to perform this action.
    
Date and time: 2023-10-22 04:45:55
     ---> Microsoft.Data.SqlClient.SqlException: Msg 297, The user does not have permission to perform this action.
    
Date and time: 2023-10-22 04:45:55
    
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 2363
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParser.cs:line 1835
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParser.cs:line 0
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData() in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlDataReader.cs:line 1349
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData() in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlDataReader.cs:line 281
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 5787
   at Microsoft.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 5665
   at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, String endMethod, Boolean isInternal) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 2927
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 2602
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 2567
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.Azure.SqlDatabase.Jobs.Core.ScriptExecutionTaskExecutor.<ExecuteBatchAsync>d__6.MoveNext()
   --- End of inner exception stack trace ---
   at Microsoft.Azure.SqlDatabase.Jobs.Core.ScriptExecutionTaskExecutor.<ExecuteBatchAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.Azure.SqlDatabase.Jobs.Core.ScriptExecutionTaskExecutor.<ExecuteAsync>d__1.MoveNext()<---

已检查作业运行用户的权限，未发现异常，寻求排查帮助。

• 确认作业执行上下文差异：手动触发作业时使用的是当前登录用户，定时调度使用的是作业定义中关联的credential对应的用户。需验证该credential用户是否拥有目标数据库中执行存储过程的EXECUTE权限，以及维护操作所需的特定权限（如ALTER INDEX、DBCC相关权限）。
• 检查作业步骤的执行身份：部分作业步骤可能单独指定了执行用户，而非默认的作业credential。需确认每个作业步骤的Run as设置，确保对应用户权限齐全。
• 验证权限的有效范围：确认作业用户的权限是否覆盖存储过程所在的schema，比如若存储过程在dbo之外的schema下，需确保用户拥有该schema的EXECUTE权限，而非仅数据库级权限。
• 排查时间相关的权限变更：检查周末期间是否有AD组权限调整、Azure SQL防火墙规则变更等情况。定时执行时可能恰好遇到权限临时回收，或防火墙限制导致身份验证间接异常。
• 启用详细执行日志：在作业代理中开启作业的详细日志，或在存储过程内添加代码记录执行时的SUSER_SNAME()和USER_NAME()，对比手动与定时执行的实际用户是否一致。
• 检查存储过程内部权限依赖：确认存储过程是否调用了其他需要更高权限的对象（如系统存储过程、跨库对象）。手动执行时当前用户可能拥有这些权限，但作业用户未被授予。
• 重新验证作业credential配置：若使用SQL用户作为credential，确认密码未过期；尝试重新创建作业关联的credential，排除配置异常。

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