这是最近项目相关和自己感兴趣的一个问题: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
总结:
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的用户上下文;
参考文献: