Reading the SQL Server Error Log Without sysadmin
A monitoring login needs to read the SQL Server error log. I don’t want to add it to sysadmin, and I don’t want to hand it VIEW SERVER STATE if all it needs is the error log.
This post is part of the Undocumented Changes in SQL Server 2022 and 2025 series. The contents page lists every part in order.
Before SQL Server 2022, granting only error-log access was less straightforward. In SQL Server 2019, the sys.sp_readerrorlog and sys.sp_enumerrorlogs wrappers allowed members of securityadmin, or a caller with VIEW SERVER STATE. In SQL Server 2022, the same wrappers also allow VIEW ANY ERROR LOG. I first noticed this while diffing changed system procedure definitions for the changed-procedures post.
Microsoft documents sys.sp_readerrorlog, and the page says SQL Server 2019 and earlier require VIEW SERVER STATE. It also says SQL Server 2022 and later require either VIEW ANY ERROR LOG or VIEW SERVER PERFORMANCE STATE.[1] I tested the wrappers because the module text I had did not mention that second permission by name.
What the wrappers check
The relevant wrapper code is brief. These are excerpts from the shipped definitions I captured from the system catalog.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- SQL Server 2019: sys.sp_readerrorlog IF (not is_srvrolemember(N'securityadmin') = 1) AND (not HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE') = 1) -- SQL Server 2022: sys.sp_readerrorlog IF (not is_srvrolemember(N'securityadmin') = 1) AND (not HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE') = 1) AND (not HAS_PERMS_BY_NAME(null, null, 'VIEW ANY ERROR LOG') = 1) -- SQL Server 2025: sys.sp_readerrorlog IF (not is_srvrolemember(N'securityadmin') = 1) AND (not HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE') = 1) AND (not HAS_PERMS_BY_NAME(null, null, 'VIEW ANY ERROR LOG') = 1) |
sys.sp_enumerrorlogs uses the same permission gate. SQL Server 2019 checks securityadmin or VIEW SERVER STATE. SQL Server 2022 and SQL Server 2025 also check VIEW ANY ERROR LOG.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- SQL Server 2019: sys.sp_enumerrorlogs IF (not is_srvrolemember(N'securityadmin') = 1) AND (not HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE') = 1) -- SQL Server 2022: sys.sp_enumerrorlogs IF (not is_srvrolemember(N'securityadmin') = 1) AND (not HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE') = 1) AND (not HAS_PERMS_BY_NAME(null, null, 'VIEW ANY ERROR LOG') = 1) -- SQL Server 2025: sys.sp_enumerrorlogs IF (not is_srvrolemember(N'securityadmin') = 1) AND (not HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE') = 1) AND (not HAS_PERMS_BY_NAME(null, null, 'VIEW ANY ERROR LOG') = 1) |
sys.sp_readerrorlog calls sys.xp_readerrorlog after the wrapper gate. sys.sp_enumerrorlogs calls sys.xp_enumerrorlogs. I did not have a T-SQL definition file for either extended stored procedure, which is expected for X type system objects in this inventory.
The test scripts below change server-level security: they create a login and grant or revoke a permission. Run them only on a test instance, and replace the placeholder password first.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- sys.sp_readerrorlog, all three tested versions if (@p2 is NULL) begin exec sys.xp_readerrorlog @p1 end else begin exec sys.xp_readerrorlog @p1,@p2,@p3,@p4 end -- sys.sp_enumerrorlogs, all three tested versions exec sys.xp_enumerrorlogs @p1 |
The new permission is part of a broader permissions change
VIEW ANY ERROR LOG was not the only permission that appeared in the SQL Server 2022 inventory. I compared the permission rows from the SQL Server 2019 and 2022 inventories and found 44 new rows.
| Base securable | New permission in SQL Server 2022 |
|---|---|
| CREDENTIAL | CONTROL |
| CREDENTIAL | REFERENCES |
| DATABASE | ALTER ANY DATABASE EVENT SESSION ADD EVENT |
| DATABASE | ALTER ANY DATABASE EVENT SESSION ADD TARGET |
| DATABASE | ALTER ANY DATABASE EVENT SESSION DISABLE |
| DATABASE | ALTER ANY DATABASE EVENT SESSION DROP EVENT |
| DATABASE | ALTER ANY DATABASE EVENT SESSION DROP TARGET |
| DATABASE | ALTER ANY DATABASE EVENT SESSION ENABLE |
| DATABASE | ALTER ANY DATABASE EVENT SESSION OPTION |
| DATABASE | ALTER ANY EXTERNAL JOB |
| DATABASE | ALTER ANY EXTERNAL STREAM |
| DATABASE | ALTER LEDGER |
| DATABASE | ALTER LEDGER CONFIGURATION |
| DATABASE | CREATE ANY DATABASE EVENT SESSION |
| DATABASE | CREATE USER |
| DATABASE | DROP ANY DATABASE EVENT SESSION |
| DATABASE | ENABLE LEDGER |
| DATABASE | EXECUTE ANY EXTERNAL ENDPOINT |
| DATABASE | VIEW CRYPTOGRAPHICALLY SECURED DEFINITION |
| DATABASE | VIEW DATABASE PERFORMANCE STATE |
| DATABASE | VIEW DATABASE SECURITY AUDIT |
| DATABASE | VIEW DATABASE SECURITY STATE |
| DATABASE | VIEW LEDGER CONTENT |
| DATABASE | VIEW PERFORMANCE DEFINITION |
| DATABASE | VIEW SECURITY DEFINITION |
| OBJECT | UNMASK |
| SCHEMA | UNMASK |
| SERVER | ALTER ANY EVENT SESSION ADD EVENT |
| SERVER | ALTER ANY EVENT SESSION ADD TARGET |
| SERVER | ALTER ANY EVENT SESSION DISABLE |
| SERVER | ALTER ANY EVENT SESSION DROP EVENT |
| SERVER | ALTER ANY EVENT SESSION DROP TARGET |
| SERVER | ALTER ANY EVENT SESSION ENABLE |
| SERVER | ALTER ANY EVENT SESSION OPTION |
| SERVER | CREATE ANY EVENT SESSION |
| SERVER | CREATE LOGIN |
| SERVER | DROP ANY EVENT SESSION |
| SERVER | VIEW ANY CRYPTOGRAPHICALLY SECURED DEFINITION |
| SERVER | VIEW ANY ERROR LOG |
| SERVER | VIEW ANY PERFORMANCE DEFINITION |
| SERVER | VIEW ANY SECURITY DEFINITION |
| SERVER | VIEW SERVER PERFORMANCE STATE |
| SERVER | VIEW SERVER SECURITY AUDIT |
| SERVER | VIEW SERVER SECURITY STATE |
The Database Engine permissions page says SQL Server 2022 exposes 292 permissions, while SQL Server 2019 exposes 248. It also says sys.fn_builtin_permissions specifies which permissions are new in recent versions.[2] Microsoft also describes the SQL Server 2022 access-control change as new granular permissions for least privilege.[3]
The Tech Community article describes how this permission applies to the error-log procedures. It says VIEW ANY ERROR LOG lets the grantee read the SQL Server and SQL Server Agent logs through sys.xp_enumerrorlogs, sys.sp_readerrorlog and sys.xp_readerrorlog. It also says VIEW SERVER STATE was the minimum permission before SQL Server 2022.[4]
The runtime test
I created one SQL login on the 2025 test instance and one login with the same name on the 2019 test instance. The examples replace the test password with a placeholder.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
USE [master]; GO CREATE LOGIN [undoc_spinoff_errlog_reader] WITH PASSWORD = N'<random strong password>', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; GO REVOKE VIEW ANY ERROR LOG FROM [undoc_spinoff_errlog_reader]; REVOKE VIEW SERVER STATE FROM [undoc_spinoff_errlog_reader]; REVOKE VIEW SERVER PERFORMANCE STATE FROM [undoc_spinoff_errlog_reader]; GO EXECUTE AS LOGIN = N'undoc_spinoff_errlog_reader'; EXEC sys.sp_readerrorlog 0, 1; REVERT; GO EXECUTE AS LOGIN = N'undoc_spinoff_errlog_reader'; EXEC sys.sp_enumerrorlogs 1; REVERT; GO GRANT VIEW ANY ERROR LOG TO [undoc_spinoff_errlog_reader]; GO EXECUTE AS LOGIN = N'undoc_spinoff_errlog_reader'; EXEC sys.sp_readerrorlog 0, 1; REVERT; GO EXECUTE AS LOGIN = N'undoc_spinoff_errlog_reader'; EXEC sys.sp_enumerrorlogs 1; REVERT; GO |
On the 2025 instance, the login could not read or enumerate the logs with no grant. Both wrappers raised error 27219.
|
1 2 |
2025 no grant: sp_readerrorlog error Msg 27219: Caller does not have permissions to execute the stored procedure. 2025 no grant: sp_enumerrorlogs error Msg 27219: Caller does not have permissions to execute the stored procedure. |
After GRANT VIEW ANY ERROR LOG, both wrappers succeeded.
|
1 2 |
2025 VIEW ANY ERROR LOG: sp_readerrorlog success rows=135 2025 VIEW ANY ERROR LOG: sp_enumerrorlogs success rows=7 |
I also tested the permission named on the current sys.sp_readerrorlog Learn page. With only VIEW SERVER PERFORMANCE STATE, HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER PERFORMANCE STATE') returned 1. HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE') returned 0. The procedure still failed with error 27219.
|
1 2 3 |
2025 VSPS: HAS_PERMS VSPS value 1 2025 VSPS: HAS_PERMS VSS value 0 2025 VIEW SERVER PERFORMANCE STATE: sp_readerrorlog error Msg 27219: Caller does not have permissions to execute the stored procedure. |
That differs from the permission requirement currently shown on the Learn page. On the build I tested, VIEW SERVER PERFORMANCE STATE alone did not satisfy the wrapper code for sys.sp_readerrorlog.
The 2019 comparison
On the 2019 instance, the same login name failed with no grant. After GRANT VIEW SERVER STATE, both wrappers succeeded.
|
1 2 3 4 |
2019 no grant: sp_readerrorlog error Msg 27219: Caller does not have permissions to execute the stored procedure. 2019 no grant: sp_enumerrorlogs error Msg 27219: Caller does not have permissions to execute the stored procedure. 2019 VIEW SERVER STATE: sp_readerrorlog success rows=207 2019 VIEW SERVER STATE: sp_enumerrorlogs success rows=7 |
That matches the 2019 wrapper definition and the current Learn page for sys.sp_readerrorlog.
Recommended permission
For a SQL Server 2022 or 2025 monitoring login that only needs the error log, the grant I use is VIEW ANY ERROR LOG.
|
1 2 3 4 5 6 7 8 9 |
USE [master]; GO CREATE LOGIN [monitor_errorlog_reader] WITH PASSWORD = N'<random strong password>', CHECK_POLICY = ON; GO GRANT VIEW ANY ERROR LOG TO [monitor_errorlog_reader]; GO |
If the same login also needs dynamic management views (DMVs), use the DMV permission it needs for that separate job. Do not use VIEW SERVER STATE as a substitute for error-log access when VIEW ANY ERROR LOG is enough.
The two test logins I created are still present. Run the cleanup command on each test instance. The post leaves the test login in place, so the cleanup command is shown separately.
|
1 |
DROP LOGIN [undoc_spinoff_errlog_reader]; |
I did not find a separate Learn page for sys.sp_enumerrorlogs. A source search found the sys.sp_readerrorlog page, references to xp_readerrorlog, and one Azure article that grants EXEC on xp_readerrorlog. The case-sensitive and case-insensitive searches did not find a sys.sp_enumerrorlogs reference page.[5]
Have you had to grant monitoring access to the SQL Server error log without broad server-state access? If you have seen a different permission requirement, share the details on Bluesky or LinkedIn.
References
- sys.sp_readerrorlog (Transact-SQL) - Microsoft Learn. Documents the arguments and the stated SQL Server 2019 and SQL Server 2022 permission requirements for reading error logs. ↩
- Permissions (Database Engine) - Microsoft Learn. Lists the Database Engine permissions and the SQL Server 2019 and SQL Server 2022 permission counts. ↩
- What’s New in SQL Server 2022 - Microsoft Learn. Summarizes SQL Server 2022 access-control changes as new granular permissions. ↩
- New granular permissions for SQL Server 2022 and Azure SQL to improve adherence with PoLP - Microsoft Tech Community. Describes
VIEW ANY ERROR LOG, the affected procedures, and the olderVIEW SERVER STATErequirement. ↩ - MicrosoftDocs/sql-docs, MicrosoftDocs/azure-docs and MicrosoftDocs/fabric-docs - GitHub. Source repositories searched with whole-word fixed-string
git grep, with case-sensitive and case-insensitive follow-up searches on 2026-09-26. ↩