Export (0) Print
Expand All
Expand Minimize

xp_sqlagent_msx_account

SQL Server 2000

  New Information - SQL Server 2000 SP3.

Sets or retrieves the SQL Server Agent MSX account user name and password to or from the local security authority(LSA) secrets on the TSX server. Execute permissions of this extended stored procedure are restricted to the securityadmin fixed server role.

SQL Server Agent must be running to execute this extended stored procedure. In addition, if the account specified is a SQL Server login, SQL ServerAgent must have local Windows administrator rights because SQL Server Agent stores the user name and password as an LSA secret, and access is restricted to local Windows administrators.

Syntax

xp_sqlagent_msx_account

    {N'GET' | N'SET' | N'DEL,

    N'MSX_domain_name', N'MSX_username', N'MSX_password'

    }

Arguments

N'GET'

Retrieves the current SQL Server Agent MSX account. N'GET' is nvarchar with no default. The password is not reported for security reasons.

N'SET'

Sets the account to be used as the SQL Server Agent MSX account. Use the MSX_username, and MSX_password parameters to specify the account to use as the SQL Server Agent MSX account. N'SET' is nvarchar with no default.

N'DEL'

Deletes the SQL Server Agent MSX account.

'MSX_domain_name'

Reserved for future use.

'MSX_username'

The name of the Windows account to be used as the SQL Server Agent MSX account. Specify an empty string for this parameter to select Windows security. In this case, the SQL Server Agent service account credentials are used to log on to the MSX server. MSX_username is nvarchar with no default.

'MSX_password'

The password for the SQL Server account specified in MSX_username. Specify an empty string for this parameter to select Windows security. In this case, the SQL Server Agent service account credentials are used to log into the MSX server. MSX_password is nvarchar with no default.

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

Return Code Values

0 (success) or 1 (failure)

When xp_sqlagent_msx_account fails and returns 1, SQL Server generates an error message with information about the error.

Result Sets

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

Column Data type Description
Domain sysname N/A. Reserved for future use.
Username sysname Account used as the SQL Server Agent MSX account.

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

Permissions

Execute permissions for xp_sqlagent_msx_account default to members of the securityadmin fixed server role.

Examples
  1. Retrieve the currently assigned SQL Server Agent MSX account

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

EXEC master.dbo.xp_sqlagent_msx_account N'GET'
  1. Set the SQL Server Agent MSX account to use Windows Authentication

This example sets the SQL Server Agent MSX account to Windows Authentication.

EXEC master.dbo.xp_sqlagent_msx_account N''SET'',
   N'',  -- Reserved for future use 
   N'',  -- MSX_username
   N''  -- MSX_password
C. Set the SQL Server Agent MSX account to SQL Server Authentication

This example sets the SQL Server Agent MSX account to Ralph and specifies a password.

EXEC master.dbo.xp_sqlagent_msx_account N'SET',
   N'',  -- Reserved for future use 
   N'Ralph',  -- MSX_username
   N'lI(3x5$9'  -- MSX_password
D. Delete the SQL Server Agent MSX account

This example deletes the SQL Server Agent MSX account. SQL Server Agent then defaults to Windows Authentication.

EXEC master.dbo.xp_sqlagent_msx_account N'DEL'

See Also

SQL Server Agent Properties (Job System Tab)

System Stored Procedures

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft