xp_sqlagent_msx_account
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
- 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'
- 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'