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