SQL Server

Four Undocumented SQL Server 2014 Agent Procedures

Four Undocumented SQL Server 2014 Agent Procedures

SQL Server 2014 supports the following useful undocumented SQL Server Agent stored procedures:

  • sp_MSgetalertinfo
  • sp_readerrorlog
  • sp_sqlagent_is_srvrolemember
  • sp_sqlagent_get_startup_info

Two of these stored procedures exist in the master database (sp_MSgetalertinfo and sp_readerrorlog),
other exist in the msdb database.

sp_MSgetalertinfo

The sp_MSgetalertinfo stored procedure is used to get information about alerts from Windows registry.

Syntax

sp_MSgetalertinfo [ @includeaddresses = ] ‘address’

Arguments

[ @includeaddresses = ] ‘address’
Is the flag, which indicates that additional alert’s information (e-mail, pager and net send addresses)
will be returned. address is bit, with default 0.

Return Code Values

None.

Result Sets

Column name Type Description
AlertFailSafeOperator nvarchar(255) The operator name.
AlertNotificationMethod int The alert notification method.
AlertForwardingServer nvarchar(255) The alert forwarding server name.
AlertForwardingSeverity int The alert forwarding severity.
AlertPagerToTemplate nvarchar(255) The pager number to copy alert’s text.
AlertPagerCCTemplate nvarchar(255) The pager number to copy alert’s text.
AlertPagerSubjectTemplate nvarchar(255) The alert pager subject template.
AlertPagerSendSubjectOnly int Indicates whether the alert send to pager only subject or not.
AlertForwardAlways int Indicates whether the alert will be forward always.
AlertFailSafeEmailAddress nvarchar(255) The e-mail address to send alert’s text.
AlertFailSafePagerAddress nvarchar(255) The pager address to send alert’s text.
AlertFailSafeNetSendAddress nvarchar(255) The net send address to send alert’s text.

Remarks

The sp_MSgetalertinfo stored procedure checks the registry settings, and should be used with caution.
This stored procedure exists in the master database.

Permissions

By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.

Examples

This is the example to get full alert information:

USE master
EXEC sp_MSgetalertinfo 1

sp_readerrorlog

The sp_readerrorlog stored procedure returns the content of the errorlog file.

Syntax

sp_readerrorlog [ @p1 = ] ‘p1’,
[ @p2 = ] ‘p2’,
[ @p3 = ] ‘p3’,
[ @p4 = ] ‘p4’

Arguments

[ @p1 = ] ‘p1’
The error log file number to read. p1 is integer, with default 0. 0 indicates that the
sp_readerrorlog procedure will return the contents of the current error log.

[ @p2 = ] ‘p2’
Is the log file type (1 – error log, 2 – SQL Agent log). p2 is integer, with default Null.
Null indicates that all error log data will be returned.

[ @p3 = ] ‘p3’
Is the string you want to search for. p3 is varchar(255), with default Null.

[ @p4 = ] ‘p4’
Is the string you want to search for to further refine the results. p4 is varchar(255),
with default Null.

Return Code Values

1 (failure).

Result Sets

Column name Type Description
LogDate datetime The log datetime value
ProcessInfo sysname The process name
Text sysname The full description of the log data

Remarks

This stored procedure exists in the master database.

Permissions

By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.

Examples

This is the example to returns all rows from the current SQL Server 2014 error log file that
contains both strings ‘Starting up database’ and ‘master’:

EXEC sp_readerrorlog 0, 1, ‘Starting up database’, ‘master’

sp_sqlagent_is_srvrolemember

The sp_sqlagent_is_srvrolemember stored procedure indicates whether a SQL Server 2014 login
is a member of the specified fixed server role. By the way, you can use the IS_SRVROLEMEMBER
function as the documented way to accomplish the same task.

Syntax

sp_sqlagent_is_srvrolemember [ @role_name = ] ‘role_name’,
[ @login_name = ] ‘login_name’

Arguments

[ @role_name = ] ‘role_name’
Is a fixed server role name. role_name is sysname, with no default.

[ @login_name = ] ‘login_name’
Is a SQL Server 2014 login name. login_name is sysname, with no default.

Return Code Values

int.

Remarks

The sp_sqlagent_is_srvrolemember stored procedure returns:
0 – when login is not a member of role, or when @role_name is null or @login_name is null.
1 – when login is a member of role.

This stored procedure exists in the msdb database.

Permissions

By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.

Examples

This is the example to check whether the Alex login is a member of the setupadmin server role:

USE msdb
DECLARE @srvrolemember INT
EXEC @srvrolemember = sp_sqlagent_is_srvrolemember @role_name = ‘setupadmin’, @login_name = ‘Alex’
SELECT @srvrolemember


sp_sqlagent_get_startup_info

The sp_sqlagent_get_startup_info stored procedure returns total SQL Server 2014 information
(such as compatibility level, case sensitive, SQL Server name, max user connection,platform,
instance name, and so on).

Syntax

sp_sqlagent_get_startup_info

Return Code Values

0 (success).

Result Sets

Column name Type Description
msdb_70_compatible int Indicates the compatibility level
msdb_read_only int Indicates whether msdb database is in a read-only access mode (1 – read-only, 0 – not read-only)
msdb_available int Indicates whether msdb database is available (1 – available, 0 – not available)
case_sensitive_server int Indicates whether SQL Server is case sensitive (1 – case sensitive, 0 – not case sensitive)
max_user_connection int The maximum user connection number
sql_server_name sysname The SQL Server name
tbu int Internal use only
platform int Indicates the hardware platform
instance_name sysname The name of the SQL Server instance
is_clustered int Indicates whether SQL Server is clustered or not (1 – clustered, 0 – not clustered)

Remarks

This stored procedure exists in the msdb database.

Permissions

By default, only members of the sysadmin fixed server role can execute this stored procedure.
The system administrator can grant the execute permission to run this procedure to other users.

Examples

This is the example to returns the total SQL Server 2014 information:

USE msdb
EXEC sp_sqlagent_get_startup_info

Note. Before executing the sp_sqlagent_get_startup_info procedure, you should enable
the use of ‘Agent XPs’ by using the sp_configure system stored procedure.

This is the example to enable the use of ‘Agent XPs’ option:

EXEC master..sp_configure ‘Agent XPs’, ‘1’
GO
RECONFIGURE;
GO