xp_sqlagent_proxy_account

Sets or retrieves the proxy account information used by SQL Server Agent and the xp_cmdshell extended stored procedure when executing jobs or commands for users who are not members of the sysadmin fixed server role. The proxy account is a Microsoft® Windows® account in whose security context the jobs or command prompt commands are run.

Syntax

xp_sqlagent_proxy_account

                   { N'GET' |

                     N'SET', N'agent_domain_name', N'agent_username', N'agent_password'

                    }

Arguments

N'GET'

Retrieves the name of the current SQL Server Agent proxy account. N'GET' is nvarchar with no default.

N'SET'

Sets the Windows account to be used as the SQL Server Agent proxy account. Use the agent_domain_name, agent_username, and agent_password parameters to specify the Windows account to use as the proxy account. If you do not specify valid Windows account information, such as not specifying the correct password, sp_sqlagent_proxy_account will receive an error. N'SET' is nvarchar with no default.

'agent_domain_name'

Is the name of the Windows domain containing the Windows user account specified in agent_username. agent_domain_name is nvarchar with no default.

'agent_username'

Is the name of the Windows account to be used as the SQL Server Agent proxy account. agent_username is nvarchar with no default.

'agent_password'

Is the password for the Windows account specified in agent_username. agent_password is nvarchar with no default.

Note  Parameters for xp_sqlagent_proxy_account must be specified in order. Named parameters cannot be used.

Return Code Values

0 (success) or 1 (failure)

When the execution of xp_sqlagent_proxy_account fails, SQL Server generates an error message with information about the error.

Result Sets

If a SQL Server Agent proxy account has been set, xp_sqlagent_proxy_account returns a result set with the following information when you specify N'GET'.

If a SQL Server Agent proxy account has not been set, or if N'SET' is specified, no result set is returned.

Remarks

SQL Server Agent proxy accounts allow SQL Server users who do not belong to the sysadmin fixed server role to execute xp_cmdshell and own SQL Server Agent jobs. The administrators can assign appropriate security permissions to the proxy account to control the ability of these jobs to access resources in the network.

When a SQL Server user executes a command prompt command using xp_cmdshell, the command must execute in the security context of a Windows account. If the SQL Server user is a member of the sysadmin fixed server role, SQL Server executes the command prompt command using the Windows account under which the SQL Server service is running. If the SQL Server user executing xp_cmdshell is not a member of the sysadmin fixed server role, SQL Server executes the command using the Windows account specified as the SQL Server Agent proxy account. If no SQL Server Agent proxy account has been set, the user gets an error. SQL Server Agent jobs also must execute in the security context of a Windows account. If the job is owned by a member of the sysadmin fixed server role, the job executes using the Windows account under which the SQL Server service is running. If the job owner is not in sysadmin, the job executes using the SQL Server Agent proxy account, and an error is raised if no proxy account has been set.

xp_sqlagent_proxy_account sets or retrieves the proxy account for the instance on which it is executed. The SQL Server service for that instance must be running under a Windows administrator account to read or set the SQL Server Agent proxy account.

Permissions

Execute permissions for xp_sqlagent_proxy_account default to members of the sysadmin fixed server role.

Examples

A. Retrieve the currently assigned SQL Server Agent proxy account

This example retrieves the account currently assigned for use as the SQL Server Agent proxy account.

EXEC master.dbo.xp_sqlagent_proxy_account N'GET'

This is the result set.

Domain               Username
------------------------------------
NETDOMAIN            john

B. Set the SQL Server Agent proxy account without a password

This example sets the SQL Server Agent proxy account to LONDON\ralph without specifying a password. This example will receive an error that the extended stored procedure cannot log in if the LONDON/ralph account actually has a password.

EXEC master.dbo.xp_sqlagent_proxy_account N'SET',
             N'NETDOMAIN', -- agent_domain_name
             N'ralph', -- agent_username
             N'' – agent password

C. Set the SQL Server Agent proxy account with a password

This example sets the SQL Server agent proxy account to LONDON\Ralph and specifies a password.

EXEC master.dbo.xp_sqlagent_proxy_account N'SET',
             N'NETDOMAIN', -- agent_domain_name
             N'ralph', -- agent_username
             N'RalphPwd', – agent password

See Also

SQL Server Agent Properties (Job System Tab)

System Stored Procedures

xp_cmdshell