Managing the Core Database Engine - Microsoft SQL Server 2008 R2 Administration - page 124

15. Click the OK button to accept the settings.
16. For this recipe, we have chosen only 3 task types: Replication Distributor, SQL
Server Integration Package, and PowerShell.
We now have successfully created the credential and proxy account to implement security for
SQL Server Agent Jobs management using the SSMS tool.
How it works...
Proxy provides the SQL Server Agent service with access to the security credentials for a
Windows user without having a need to assign elevated privileges for the users. A job step that
uses a proxy can access the specified job subsystems (as we assigned previously) by using the
security context.
SQL Server Agent checks the subsystem access for a proxy and gives access to the proxy
each time the job step runs. If the proxy no longer has access to the subsystem, the job step
fails. The end result of the job can be obtained by referring to the corresponding job details,
right-click on job | view history. Similarly, we can query sysJobHistory table, which
contains information about the execution of scheduled jobs by SQL Server Agent. Otherwise,
SQL Server Agent impersonates the user that is specified in the proxy and runs the job step.
However, when you have a job step that executes TSQL, then it will not use SQL Server Agent
proxies, instead TSQL job steps run in the security context of the owner of the job. To avoid
any errors or interruption to that job step due to the job owner permissions, you can set the
security context for that TSQL job step using sp_add_jobstep stored procedure by the
passing value to database_user_name parameter.
The user specified within the credentials and proxy creation must have permission to connect
to that instance of SQL Server. A user must have access to a proxy to use the proxy in a job
step. Access can be granted to three types of security principals: SQL Server logins, Server
roles, and Roles within the MSDB database. Additionally, if a new job is specified in the
credentials, then that user must have Log on as a batchjob Windows security permission
on the computer on which SQL Server is running.
There's more...
In case your SQL Server instance is upgraded from version 2000 to 2008, or 2008 R2, during
the upgrade process, all the user proxy accounts that existed on SQL Server 2000 will be
changed to the temporary global proxy account UpgradedProxyAccount . This temporary
global proxy account will have access to those subsystems that were explicitly used, and will
not have access to all subsystems after upgrading.