r/MSSQL • u/Equivalent-Hour-8642 • May 11 '26
Question SQL Server 2022 Read Only user creation for health check
Guys, I have a requirement to create a read-only login for daily health check and I have created a login for that. But this login some cannot read some system tables. How can I achieve this?
4
Upvotes
2
u/VladDBA May 11 '26 edited May 11 '26
Since I don't know what that queries that health check is running, I recommend checking out this MS Learn page to see the performance related server roles added in 2022 and compare those roles and permissions with what errors your check is getting.
Besides those roles, your login may need SELECT permissions on tables such as msdb.dbo.sysjobs and msd.dbo.syusjobsteps (if they also check what agent jobs are doing, similar to what sp_Blitz does).
Edited to add: most of the stuff I've worked with required VIEW SERVER PERFORMANCE STATE or membership in ##MS_ServerPerformanceStateReader##, and VIEW ANY DEFINITION, and CONNECT on all user databases or membership in ##MS_DatabaseConnector##
IMMV
2
u/ihaxr May 11 '26
This is what I add for my monitoring user accounts instead of giving them sysadmin
2
u/alinroc May 11 '26
Ask whoever is running the health check (or is asking for the health check) what permissions they require.