SQL Server Agent Subsystems
A subsystem is a predefined object that represents a set of functionality available to a Microsoft SQL Server Agent proxy. Each proxy has access to one or more subsystems. Subsystems provide security because they delimit access to the functionality that is available to a proxy. Each job step runs in the context of a proxy, except for Transact-SQL job steps. Transact-SQL job steps use the EXECUTE AS command to set the security context.
Note
After upgrading from SQL Server 2000 to SQL Server 2005, all user proxy accounts that existed before upgrading are changed to the temporary global proxy account UpgradedProxyAccount. The UpgradedProxyAccount is only granted access to those subsystems that were explicitly used, and does not have access to all subsystems after upgrading.
Microsoft SQL Server 2005 defines the subsystems listed in the following table:
| Subsystem name | Description |
|---|---|
Microsoft ActiveX Script |
Run an ActiveX scripting job step. |
Operating System (CmdExec) |
Run an executable program. |
Replication Distributor |
Run a job step that activates the replication Distribution Agent. |
Replication Merge |
Run a job step that activates the replication Merge Agent. |
Replication Queue Reader |
Run a job step that activates the replication Queue Reader Agent. |
Replication Snapshot |
Run a job step that activates the replication Snapshot Agent. |
Replication Transaction Log Reader |
Run a job step that activates the replication Log Reader Agent. |
Analysis Services Command |
Run an Analysis Services command. |
Analysis Services Query |
Run an Analysis Services query. |
SSIS package execution |
Run an SSIS package. |
Note
Because Transact-SQL job steps do not use proxies, there is no SQL Server Agent subsystem for Transact-SQL job steps.
SQL Server Agent enforces subsystem restrictions even when the security principal for the proxy would normally have permission to run the task in the job step. For example, a proxy for a user that is a member of the sysadmin fixed server role cannot run an SSIS job step unless the proxy has access to the SSIS subsystem, even though the user can run SSIS packages.
See Also
Tasks
How to: Create an ActiveX Script Job Step (SQL Server Management Studio)
How to: Create a CmdExec Job Step (SQL Server Management Studio)
Other Resources
CREATE CREDENTIAL (Transact-SQL)
EXECUTE AS Clause (Transact-SQL)
Replication Agent Security Model
sp_grant_proxy_to_subsystem (Transact-SQL)
sp_revoke_proxy_from_subsystem (Transact-SQL)