patternsqlMajor
What's the owner of SqlServerAgent job for?
Viewed 0 times
ownerthewhatsqlserveragentforjob
Problem
What is the possible use of owner of an SQL Server Agent job, I wonder?
Removed, and left it blank and the job still succeeds
Removed, and left it blank and the job still succeeds
Solution
The job owner determines the execution context of the job's steps.
For a T-SQL job step: If the job owner is a sysadmin then the step will execute as either the SQL Server Agent service account or a specified database user (set on the Advanced page of the Job Step Properties dialog). If the job owner is not a sysadmin then the step will execute as the job owner.
For other job steps: If the job owner is a sysadmin then the step will execute as either the SQL Server Agent service account or a specified Proxy. If the job owner is not a sysadmin then the step will execute as a specified Proxy (which requires the job owner to have permission to use the proxy).
A SQL Agent Proxy is created based on a SQL Server Credential which stores the secret associated with some external security principal. For example, sysadmin Kim creates a Credential for ADVENTUREWORKS\Bob and then creates a Proxy based on that Credential. She makes the Proxy active for the CmdExec subsystem. She gives (nonsysadmin) Qin the rights to use that Proxy. Qin can now create a CmdExec job step that executes as ADVERNTUREWORKS\Bob.
See Implement SQL Server Agent Security in Books Online.
For a T-SQL job step: If the job owner is a sysadmin then the step will execute as either the SQL Server Agent service account or a specified database user (set on the Advanced page of the Job Step Properties dialog). If the job owner is not a sysadmin then the step will execute as the job owner.
For other job steps: If the job owner is a sysadmin then the step will execute as either the SQL Server Agent service account or a specified Proxy. If the job owner is not a sysadmin then the step will execute as a specified Proxy (which requires the job owner to have permission to use the proxy).
A SQL Agent Proxy is created based on a SQL Server Credential which stores the secret associated with some external security principal. For example, sysadmin Kim creates a Credential for ADVENTUREWORKS\Bob and then creates a Proxy based on that Credential. She makes the Proxy active for the CmdExec subsystem. She gives (nonsysadmin) Qin the rights to use that Proxy. Qin can now create a CmdExec job step that executes as ADVERNTUREWORKS\Bob.
See Implement SQL Server Agent Security in Books Online.
Context
StackExchange Database Administrators Q#53362, answer score: 22
Revisions (0)
No revisions yet.