Database Mail - permission problem

SQL Server Administration
1

Hello,

I have a problem with Database Mail. Thank you for help ...

SQL Server 2016 running on Windows Server 2012 R2

When I try send test message i have problem in application log: "Error Message: The update to the database failed. Reason: The EXECUTE permission was denied on the object 'sysmail_logmailevent_sp' database 'msdb' , schema 'dbo'. "

I try lots of tips and nothing solved :

a) SQLAgent and the user is logged on MngStudio are member DatabaseMailUserRole, SysAdmin

b) I try ...
GRANT EXECUTE ON OBJECT :: sp_send_dbmail TO DatabaseMailUserRole
GRANT EXECUTE ON OBJECT :: sp_readrequest TO DatabaseMailUserRole
GRANT EXECUTE ON OBJECT :: sysmail_logmailevent_sp TO DatabaseMailUserRole
GRANT EXECUTE ON OBJECT :: sysmail_help_configure_sp TO DatabaseMailUserRole

c) Result next command no problem

EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

d) SQL Broker running

e) SQLAgent service i try run under the Administrator account

I'll be glad for any help!

----- After NEXT TESTING !

Hello, I tested this weekend and here's the result ...

If I login Service by SQL configurator Database Engine and Agent as a Domain \ Administrator
Email sending messages and running.

So i mean some different Local System vs. Domain\Administrator.

I view security (rsop.msc etc.) and I found the following:

Server :
Local Security Policy AND Domain Security Policy

Bypass traverse checking (SeChangeNotifyPrivilege) : Have Administrator+NT SERVICE\MSSSQLSERVER

  • Local Service + Network Service + Agent + i dont see SYSTEM
    Replace a process-level token (SeAssignPrimaryTokenPrivilege) : Have Administrator+NT SERVICE\MSSSQLSERVER
  • Local Service + Network Service + Agent + i dont see SYSTEM
    Adjust memory quotas for a process (SeIncreaseQuotaPrivilege) : Have Administrator+NT SERVICE\MSSSQLSERVER
  • Local Service + Network Service + Agent + i dont see SYSTEM

Local Security Policy
Log on as a service (SeServiceLogonRight)- Have Administrator+NT SERVICE\MSSSQLSERVER

  • Local Service + Network Service + Agent + SYSTEM

Domain Security Policy
Log on as a service (SeServiceLogonRight) - Undefined

I suppose on Local system missing "something" that have Domain\Administrator, and therefore email
not sended ... I note that the SQL server runs normally and it works. The server still goes File Server
service also functioning without problems.

Thanks for tips !

Kyssling

4

I finish topic - because i now know necessary for the Database Mail
use the service NT\SERVICE, and not the Local System.
(https://technet.microsoft.com/en-us/library/ms191442(v=sql.105).aspx?f=255&MSPPError=-2147217396)

  • Unfortunatelly I have to open a new topic :frowning: