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

The topic describes the SA0146 analysis rule.

Message

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

Description

The rule checks RAISERROR statements for having severity above 18 and not having a WITH LOG clause.

Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.

Severity levels from 0 through 18 can be specified by any user.

Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions.

For severity levels from 19 through 25, the WITH LOG option is required.

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

Should be:

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

How to fix

Add WITH LOG clause to the RAISERROR statement.

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