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 an
Operating System (CmdExec) step. The job continuously fails on this step and throws the
following error message: “The user does not have sufficient permission to perform the
operation.”
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