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

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:

SQL
Copy
1-- Example of problematic query
2RAISERROR('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:

SQL
Copy
1-- Corrected query with logging
2RAISERROR('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.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1RAISERROR('Invalid Type ''%s''',19, 22, 'type') ;
 2
 3
 4RAISERROR('Invalid Type ''%s''',19, 22, 'type') WITH LOG,NOWAIT,SETERROR;
 5
 6RAISERROR('Invalid Type ''%s''',19, 22, 'type') WITH LOG;
 7
 8RAISERROR('Invalid Type ''%s''',18, 22, 'type') ;
 9
10RAISERROR('Invalid Type ''%s''',19, 22, 'type') WITH NOWAIT,SETERROR;
Example Test SQL with Automatic Fix
SQL
Copy
 1RAISERROR('Invalid Type ''%s''',19, 22, 'type') WITH LOG ;
 2
 3
 4RAISERROR('Invalid Type ''%s''',19, 22, 'type') WITH LOG,NOWAIT,SETERROR;
 5
 6RAISERROR('Invalid Type ''%s''',19, 22, 'type') WITH LOG;
 7
 8RAISERROR('Invalid Type ''%s''',18, 22, 'type') ;
 9
10RAISERROR('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
See Also

Other Resources

Analysis Rules