SCOM Discovery Error The EXECUTE permission was denied on the object 'SQLAGENT_SUSER_SNAME', database 'msdb', schema 'dbo'.

Question

Hello Mates,

We have SCOM 2019 Installed and configured in our environment , But for some of SQL Server we are seeing below Discovery Error.
Could some one help me to sort out and the way how to fix the issue ?

"The EXECUTE permission was denied on the object 'SQLAGENT_SUSER_SNAME', database 'msdb', schema 'dbo'."

Thanks in advance..

Answer 1

• got this as well, there's a new script from @Kevin Holman that fixes it* I just highlighted in bold below the permission you are missing

--(SQL2012 and later)
--Begin Query
USE [master];
SET NOCOUNT ON;
DECLARE @accountname sysname = 'NT SERVICE\HealthService';
-- Create the server role and grant instance level permissions
CREATE SERVER ROLE [SCOM_HealthService];
GRANT VIEW ANY DATABASE TO [SCOM_HealthService];
--GRANT ALTER ANY DATABASE TO [SCOM_HealthService]; --Required only for SCOMDB tasks
GRANT VIEW ANY DEFINITION TO [SCOM_HealthService];
GRANT VIEW SERVER STATE TO [SCOM_HealthService];
DECLARE @createLoginCommand nvarchar(200);
SET @createLoginCommand = ' CREATE LOGIN '+ QUOTENAME(@accountname) +' FROM WINDOWS WITH DEFAULT_DATABASE=[master];'
EXEC(@createLoginCommand);
-- Add the login to the user defined server role
EXEC sp_addsrvrolemember @loginame = @accountname, @rolename = 'SCOM_HealthService'
-- Add the login and database role to each database
DECLARE @createDatabaseUserAndRole nvarchar(max) SET @createDatabaseUserAndRole = '';
SELECT @createDatabaseUserAndRole = @createDatabaseUserAndRole + ' USE ' + QUOTENAME(db.name) + '; CREATE USER ' + QUOTENAME(@accountname) + ' FOR LOGIN ' + QUOTENAME(@accountname) + '; CREATE ROLE [SCOM_HealthService]; EXEC sp_addrolemember @rolename = ''SCOM_HealthService'', @membername = '+ QUOTENAME(@accountname) + ''
-- 'ALTER ROLE [SCOM_HealthService] ADD MEMBER ' + QUOTENAME(@accountname) + ';'
FROM sys.databases db
LEFT JOIN sys.dm_hadr_availability_replica_states hadrstate ON db.replica_id = hadrstate.replica_id
WHERE db.database_id != 2 AND db.user_access = 0 AND db.state = 0 AND db.is_read_only = 0 AND (hadrstate.role = 1 or hadrstate.role IS NULL);
EXEC(@createDatabaseUserAndRole)
-- Add database specific permissions to database role
USE [master];
GRANT EXECUTE ON sys.xp_readerrorlog TO [SCOM_HealthService]
GRANT SELECT ON sys.database_mirroring_witnesses TO [SCOM_HealthService];
USE [msdb];
GRANT SELECT ON [dbo].[sysjobschedules] TO [SCOM_HealthService];
GRANT SELECT ON [dbo].[sysschedules] TO [SCOM_HealthService];
GRANT SELECT ON [dbo].[sysjobs_view] TO [SCOM_HealthService];
GRANT SELECT ON [dbo].[syscategories] TO [SCOM_HealthService];
GRANT SELECT ON [dbo].[log_shipping_primary_databases] TO [SCOM_HealthService];
GRANT SELECT ON [dbo].[log_shipping_secondary_databases] TO [SCOM_HealthService];
GRANT SELECT ON [dbo].[log_shipping_monitor_history_detail] TO [SCOM_HealthService];
GRANT SELECT ON [dbo].[log_shipping_monitor_secondary] TO [SCOM_HealthService];
GRANT SELECT ON [dbo].[log_shipping_monitor_primary] TO [SCOM_HealthService];
GRANT EXECUTE ON [dbo].[sp_help_job] TO [SCOM_HealthService];
GRANT EXECUTE ON [dbo].[sp_help_jobactivity] TO [SCOM_HealthService];
GRANT EXECUTE ON [dbo].[SQLAGENT_SUSER_SNAME] TO [SCOM_HealthService];
EXEC sp_addrolemember @rolename='SQLAgentReaderRole', @membername='SCOM_HealthService';
EXEC sp_addrolemember @rolename='PolicyAdministratorRole', @membername='SCOM_HealthService';
--End of Query