Creating a proxy

In this recipe, we will create a SQL Server proxy.

Getting ready

In this recipe, we will map out our SQL Server Agent service account (QUERYWORKS\sqlagent) to the credential we created in the previous recipe, filemanagercred. We are also going to grant this proxy with rights to run the PowerShell agent steps and operating system (CmdExec) steps. The equivalent T-SQL statements of what we are trying to achieve are as follows:

EXEC msdb.dbo.sp_add_proxy 
@proxy_name = N'filemanagerproxy',
@credential_name = N'filemanagercred', 
@enabled = 1,
@description = N'Proxy Account for PowerShell Agent Job steps'

EXEC msdb.dbo.sp_grant_login_to_proxy 
@proxy_name = N'filemanagerproxy',
@login_name = N'QUERYWORKS\sqlagent'

-- PowerShell subsystem
EXEC msdb.dbo.sp_grant_proxy_to_subsystem 
@proxy_name = N'filemanagerproxy',
@subsystem_id = 12

-- CmdExec subsystem
EXEC msdb.dbo.sp_grant_proxy_to_subsystem 
@proxy_name = N'filemanagerproxy',
@subsystem_id = 12

You can substitute this with...