SA0146 : The RAISERROR statement with severity above 18 and requires WITH LOG clause

Introduction

Ensure proper use of high-severity error handling In T-SQL code.

Description

The RAISERROR statements with a severity level above 18 require special consideration. These levels indicate serious system errors and are restricted to high-level users due to their potential impact. Failing to log such errors with the WITH LOG option can result in lost diagnostics, making it difficult to troubleshoot system issues effectively.

For example:

-- Example of problematic query
RAISERROR('Invalid Type ''%s''',19, 22, 'type');

This query specifies a severity level of 19 but lacks the WITH LOG clause. Without logging, tracking and resolving critical errors can become a challenge.

Errors with severity 19 and above are critical and need logging for proper monitoring and troubleshooting.
Only sysadmin role members or users with necessary permissions should specify these higher severity levels, ensuring controlled error management.

How to fix

Ensure proper logging of high-severity errors in T-SQL code using the WITH LOG clause in RAISERROR statements.

Follow these steps to address the issue:

1.Identify RAISERROR statements with a severity level of 19 or higher in your T-SQL code that lack the WITH LOG clause.

2.Add the WITH LOG clause to these RAISERROR statements to ensure that critical errors are logged for effective troubleshooting and monitoring.

3.Verify that only users with the appropriate sysadmin role or necessary permissions are specifying these higher severity levels.

For example:

-- Corrected query with logging
RAISERROR('Invalid Type ''%s''', 19, 22, 'type') WITH LOG;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

20 minutes per issue.

Additional Information

RAISERROR (Transact-SQL)

Example Test SQL

RAISERROR('Invalid Type ''%s''',19, 22, 'type') ;


RAISERROR('Invalid Type ''%s''',19, 22, 'type') WITH LOG,NOWAIT,SETERROR;

RAISERROR('Invalid Type ''%s''',19, 22, 'type') WITH LOG;

RAISERROR('Invalid Type ''%s''',18, 22, 'type') ;

RAISERROR('Invalid Type ''%s''',19, 22, 'type') WITH NOWAIT,SETERROR;

Example Test SQL with Automatic Fix

RAISERROR('Invalid Type ''%s''',19, 22, 'type') WITH LOG ;


RAISERROR('Invalid Type ''%s''',19, 22, 'type') WITH LOG,NOWAIT,SETERROR;

RAISERROR('Invalid Type ''%s''',19, 22, 'type') WITH LOG;

RAISERROR('Invalid Type ''%s''',18, 22, 'type') ;

RAISERROR('Invalid Type ''%s''',19, 22, 'type') WITH NOWAIT,SETERROR, LOG;

Analysis Results

	Message	Line	Column
1	SA0146 : Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.	1	0
2	SA0146 : Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.	10	0

Analysis Rules