In this recipe, we will create a SQL Server proxy.
In this recipe, we will map 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 rights to run the PowerShell agent steps and the Operating System (CmdExec) steps. Here's the T-SQL equivalent of what we are trying to achieve:
EXEC msdb.dbo.sp_add_proxy @proxy_name = N'filemanagerproxy', @credential_name = N'filemanagercredential', @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 known SQL Server principals...