DRAG DROP
You administer a Microsoft SQL Server 2008 R2 database instance. The service account
used by SQL Server services must not have administrative permissions.
You configure a new SQL Server Agent job to run every night. One of the steps in the job
runs a PowerShell step. The job continuously fails on this step and throws the following error
message:
“The process could not be created for step 1 of job (reason: A required privilege is not held
by the client). The step failed.”
You need to ensure that the SQL Server Agent Job executes successfully.
Which four actions should you perform in sequence? (To answer, move the appropriate
actions from the list of actions to the answer area and arrange them in the correct order.)
Answer: 
Explanation:
* Credentials provide SQL Server authenticated users with an identity outside of SQL
Server, on the local machine or on the network domain.
Credentials can also be used when a SQL Server authenticated user needs access to a
domain resource, such as a file location to store a backup.
To get Credential object properties, users can be a member of the public fixed server role.
A SQL Server Agent proxy defines the security context for a job step. A proxy provides SQL
Server Agent with access to the security credentials for a Microsoft Windows user. Each
proxy can be associated with one or more subsystems. A job step that uses the proxy can
access the specified subsystems by using the security context of the Windows user. Before
SQL Server Agent runs a job step that uses a proxy, SQL Server Agent impersonates the
credentials defined in the proxy, and then runs the job step by using that security context.
Why does SQL Server Agent need proxy accounts? Every job step executes under a
specific set of credentials that defines its execution context. It would be wrong for SQL
Server Agent to let an average user run his job under the credentials of the SQL Server
Agent service account. If this happened, the user could execute dangerous operating system
commands, and see and modify SQL Server data not normally accessible. SQL Server
Agent has no access to the job owner’s password, so it cannot impersonate a job owner
directly. Therefore SQL Server Agent needs to rely on a known set of credentials and a
mapping that instructs SQL Server Agent to use these credentials on behalf of the user for a
given subsystem task. This logical mapping is provided through a proxy account, that is, an
account to be used as a proxy for the user. Most subsystems, except T-SQL, use proxy
accounts.
By itself, the proxy account object does not store usernames and passwords. The account
needs
to be mapped to a specific credential object that contains the username and password. The
proxy account also needs to be associated with a subsystem that is going to use
impersonated context for task execution. Finally, a proxy account needs to be tied to a user,allowing the user to create tasks belonging to a subsystem to be run under the
aforementioned set of credentials