Can't configure MS SQL user to have enough permissions for monitoring

[EHRETic]

Hi there,

I have some difficulties to make the MS SQL monitoring work on my database server.

So as mentioned here : https://git.zabbix.com/projects/ZBX/...odbc/README.md, I have:
- created a SQL user dedicated for that specific purpose
- granted "View Server State" and "View Any Definition" permissions
- installed the ODBC drivers on Zabbix server
- configure the macros {$MSSQL.USER} and {$MSSQL.PASSWORD} in the template

But I'm not able to run those commands when setting up permissions:

GRANT SELECT ON OBJECT::msdb.dbo.sysjobs TO "Zabbix"
GRANT SELECT ON OBJECT::msdb.dbo.sysjobservers TO "Zabbix"
GRANT SELECT ON OBJECT::msdb.dbo.sysjobactivity TO "Zabbix"
GRANT EXECUTE ON OBJECT::msdb.dbo.agent_datetime TO "Zabbix"

I get the following (sorry, my console is in French and it mixes the languages):

Msg 15151, Niveau 16, État 1, Ligne 1
Cannot find the user 'Zabbix', because it does not exist or you do not have permission.
Msg 15151, Niveau 16, État 1, Ligne 2
Cannot find the user 'Zabbix', because it does not exist or you do not have permission.
Msg 15151, Niveau 16, État 1, Ligne 3
Cannot find the user 'Zabbix', because it does not exist or you do not have permission.
Msg 15151, Niveau 16, État 1, Ligne 4
Cannot find the user 'Zabbix', because it does not exist or you do not have permission.

As the article is mentioning several links to MS articles, it confuses me because I don't understand if the lines above are supposed to configure the same as mentioned in the "Configure a User to Create and Manage SQL Server Agent Jobs" or if it has to be done in extra.

The user I'm using is member of MS SQL sysadmin role so it should have enough permissions.

Also, the mention "Note! Credentials in the odbc.ini do not work for MSSQL." is confusing too!

[EHRETic]

Hi again,

I managed to give the 3 first permission but I can't find dbo.agent_datetime in msdb database. SQL agent is installed and working (Don't know if it has anything to with it)

Help pleeeez, it is still not working

[EHRETic]

Hi,

Tried to give sysadmin permissions, still the same result. Any more idea?

Thx

[EHRETic]

Login to SQL Server Management Studio. In Object Explorer on the left pane, expand the Databases folder and select the concerned database and navigate to the by expanding Security and Users folders. Right-click the User to which you want to GRANT or REVOKE the permissions.

My user is currently sysadmin, which is basically god on the SQL server, so he should have all required accesses.
But I've tried, just to be sure, to add extra permissions on a specific DB but it didn't help.

Any other idea? Thx

[Robert926]

To grant permissions for the user, switch to the Object Permissions tab. In the Objects block, select the database object on which you want to grant privileges. In the Available Privileges block, select the permissions to be assigned and click Save.

[Robert926]

Right-click a stored procedure and select Properties. In the Stored Procedure Properties -stored_procedure_name dialog box, under select a page, select Permissions. Use this page to add users or roles to the stored procedure and specify the permissions those users or roles have

[benja_naranjo]

The solution is fair simple, first you need to add the zbx_monitor login to the database msdb (in system databases).

To do it:
1. Go to the MSDB, in System databases
2. Open the MSDB folders, and in the security folder, right click and choose NEW USER
3. Select USER with SQL Login
4. Add your zbx_monitor login, and give it a USER NAME, the schema can be left blank (or select datareader)
5. Save and run again the commands. (but first type "use msdb")

[ninabsd]

Hi guys!

I found solution for this problem!

First, execute this permissions in your db MSSQL with user Admin:

use msdb;
CREATE LOGIN zbx_monitor WITH PASSWORD = 'zabbix';
GO
CREATE USER zbx_monitor FOR LOGIN zbx_monitor;
GO

--

USE [master]
GO
GRANT VIEW SERVER STATE TO [zbx_monitor];
GRANT VIEW ANY DEFINITION TO [zbx_monitor];
GRANT CREATE ANY DATABASE TO [zbx_monitor] WITH GRANT OPTION;
GRANT VIEW ANY DATABASE TO [zbx_monitor] WITH GRANT OPTION;
ALTER SERVER ROLE processadmin ADD MEMBER zbx_monitor;
GRANT ALTER TRACE TO [zbx_monitor];

USE [msdb]
GO
GRANT SELECT ON dbo.sysjobs TO [zbx_monitor];
GRANT SELECT ON dbo.sysjobhistory TO [zbx_monitor];
GRANT SELECT ON dbo.sysjobactivity TO [zbx_monitor];

EXECUTE sp_addrolemember N'SQLAgentUserRole' , N'zbx_monitor';
go

Second, change the select on item master Job Status to this select:

SELECT sj.name AS JobName
, sj.enabled AS Enabled
, sjs.run_status AS RunStatus
, sjs.message AS LastRunStatusMessage
, sjs.run_duration/10000*3600 + sjs.run_duration/100%100*60 + sjs.run_duration%100 AS RunDuration
, CASE sjs.run_date
WHEN 0 THEN NULL
ELSE (SELECT CAST(CAST(sjs.run_date AS CHAR(8))+' '+STUFF(STUFF(sjs.run_time,3,0,':'),6,0,':') AS DATETIME))
END AS LastRunDateTime
, sja.next_scheduled_run_date AS NextRunDateTime
FROM msdb..sysjobs AS sj
LEFT JOIN msdb..sysjobhistory AS sjs ON sj.job_id = sjs.job_id
LEFT JOIN ( SELECT job.job_id,
max(act.session_id) AS s_id,
max(act.next_scheduled_run_date) AS next_scheduled_run_date
FROM msdb..sysjobs AS job
LEFT JOIN msdb..sysjobactivity AS act ON act.job_id = job.job_id
GROUP BY job.job_id ) AS sja ON sja.job_id = sj.job_id
WHERE Enabled = 1

Then, voilà!! The information of jobs now is collected by zabbix.