SQL Server ->> 关于SQL Server Agent Job执行步骤时的用户上下文(User Context)问题

时间:2022-03-27 05:10:32

这是最近项目相关和自己感兴趣的一个问题:SQL Server Agent Job有几种方法可以以特定用户上下文去执行任务步骤的?

这个事情需要分几种情况来说,因为对于不同类型的任务步骤,SQL Server Agent Job是不同对待的。

做几个实验来实验SQL Server Agent Job对于不同配置先对于用户上下文的处理。

首先创建一张测试表

USE [tempdb]
GO CREATE TABLE [dbo].[Table_1](
[col1] [nvarchar](500) NULL
) ON [PRIMARY] GO

创建测试存储过程

CREATE PROCEDURE dbo.usp_TestExecuteAsUser
AS
SET NOCOUNT ON INSERT tempdb.dbo.Table_1(col1)
SELECT 'ORIGINAL_LOGIN = "' + ORIGINAL_LOGIN() + '", SUSER_NAME = "' + SUSER_NAME() + '"'
GO

创建测试Login

USE [master]
GO CREATE LOGIN [Tester1] WITH PASSWORD=N'', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

把SQL Server Agent服务器的服务账户设置为Local System

SQL Server ->> 关于SQL Server Agent Job执行步骤时的用户上下文(User Context)问题

总结:

1)Job Step类型为T-SQL,Job Owner是SYS_ADMIN成员,如果没有指定Run As,执行用户是Agent Service Account;

2)Job Step类型为T-SQL,Job Owner是SYS_ADMIN成员,如果指定Run As,执行用户是Run As;

3)Job Step类型为T-SQL,Job Owner是不是SYS_ADMIN成员(比如Public),不管有没有指定Run As,执行用户是Job Owner;

4)Job Owner是Login,Run As其实你指定的数据库的用户。也就是说,如果Job Step中调用的存储过程的代码访问了诸如像msdb的数据库资源,或者你的存储过程本身就是别的数据库的。从下面的表中你可以看到ORIGINAL_LOGIN的值是<domain>\<machine_name>$,像这样的用户根本就不是现有SQL Server服务器上能够看到的,权限也应该就是Public的权限,是会有报权限不足的错误的风险。本身Impersonate的就是数据库用户的身份。

  Agent Service Account Job Owner Job Step Type Run As ORIGINAL_LOGIN SUSER_NAME Error Message
CASE 1 Local System sa_account Transact-SQL   <domain>\<machine_name>$ NT AUTHORITY\SYSTEM  
CASE 2 Local System Tester1 Transact-SQL       'EXECUTE AS LOGIN' failed for the requested login 'Tester1'.  The step failed.
CASE 3 Local System sa_account Transact-SQL Tester1     Executed as user: Tester1. The EXECUTE permission was denied on the object 'usp_TestExecuteAsUser', database 'TestDB', schema 'dbo'. [SQLSTATE 42000] (Error 229).  The step failed.
CASE 4 Local System Tester1 Transact-SQL Tester1     Executed as user: Tester1. The EXECUTE permission was denied on the object 'usp_TestExecuteAsUser', database 'TestDB', schema 'dbo'. [SQLSTATE 42000] (Error 229).  The step failed.
CASE 5 Local System Tester1 Transact-SQL sa_account     Executed as user: Tester1. The EXECUTE permission was denied on the object 'usp_TestExecuteAsUser', database 'TestDB', schema 'dbo'. [SQLSTATE 42000] (Error 229).  The step failed.

5)Job Step类型不是T-SQL(比如SSIS Package),Job Owner是SYS_ADMIN成员,如果没有指定Run As某个Proxy,执行用户是Agent Service Account;

6)Job Step类型不是T-SQL(比如SSIS Package),Job Owner是SYS_ADMIN成员,如果指定Run As某个Proxy,执行用户是Proxy的用户上下文;

参考文献:

SQL Server Agent jobs and user contexts