SQL Server Agent & sp_send_dbmail failing on permissions

Posted on

Question :

I have set up a SQL Server Agent job to call sp_send_dbmail with a very simple select on a certain table in a certain database. Within the properties > permissions of the target database, the Database Role ‘public’ has select permission. The job runs fine, the email arrives, all good.

The problem is, if I turn off the worryingly generous ‘public’ select permission and try to add the SQLServerAgent user (which is the user the agent is running under and the user that owns the agent job) and give them select permissions, the job fails with this error message:

Failed to initialize sqlcmd library with error number -2147467259.
[SQLSTATE 42000] (error 22050)

Can anyone shed any light on this for me? I suspect that maybe a different user is involved in some way. I cannot get SQL Server Profiler to run (it’s not installed) and I don’t have access to the server to install it.

Answer :

It seems that the SQL server agent User has some problem to run sql cmd.
It uses sql cmd to run the query to attach in the email.

Please try to to give the agent user the database mail user role.
exec sp_addrolemember ‘DatabaseMailUserRole’, ‘domainagentuser’

I believe there are two situations that should be addressed in your question:

  • A SQL Server Agent account lacking the appropriate permissions;
  • A user without enough privileges to send the email you wish.

SQL Server Agent account

As you can check in this answer for another question, the SQL Server Agent service account should be a member of the sysadmin server role, therefore no restrictions should exists for that account. Please, verify that you have it properly configured.

User without enough privileges to send the email

I was able to reproduce your error (when using an account that is not the SQL Server Agent account) in my lab environment and I could correct it like this:

--After creating a SQL Server login ColumboMail I ran the following:
USE msdb;
CREATE USER ColumboMail FOR LOGIN ColumboMail;
ALTER ROLE DatabaseMailUserRole ADD MEMBER [ColumboMail];

CREATE USER ColumboMail FOR LOGIN [ColumboMail];
GRANT SELECT ON MyTable TO [ColumboMail];

The Database Mail Configuration Objects doc says

Additionally, EXECUTE permission on the sp_send_dbmail stored
procedure is only granted to members of the DatabaseMailUserRole. A
system administrator must add the user to the DatabaseMailUserRole
database role for the user to send e-mail messages.

The comments about the service account not being sysadmins is most likely not your problem. Agent will refuse to even start if it isn’t sysadmin. I.e., if the agent service is running, you know it is sysadmin. I just tried a couple of things (including making the Agent login a user in msdb and there being db_owner) to get Agent started without being sysadmin, and whatever I tried made the Agent service refuse to start.

But, by all means, check and double-check that Agent is indeed sysadmin. I can think of two, very unlikely IMO, cases where you do have Agent running as not a sysadmin:

  • Agent has a combo of privileges high enough to start but not being sysadmin. As I noted above, being a user in msdb and there being db_owner isn’t enough. So it has to be something really radical. Perhaps having CONTROL SERVER but not being sysadmin? Anyhow, not likely, at all.
  • You removed sysadmin privileges while agent is running. And you didn’t re-start it since. So, the checks made at startup passed since agent was sysadmin at that time. This assumes that you removed sysadmin from Agent and didn’t re-start agent since that. IMO, not likely either, but plausible. I’m not sure how Agent reacts if this happens, but it sure isn’t a supported config (obviously 🙂 ).

Btw, you don’t need Profiler to trace. Use the modern trace engine instead: Extended Events. Here are my tips on how to get started with XE: http://sqlblog.karaszi.com/tips-for-getting-started-with-extended-events/

Now, trying to be productive:

I’ve seen similar error messages when the machine wasn’t re-started since SQL Server was installed. The reason being that the OS PATH environment variable doesn’t include the path to SQLCMD. This was at least 20 years ago, so memory it a bit hazy, but I have a distinct recollection of this. Back then SQLCMD was installed with the engine. Not, I think it is installed by client components (but I’m not sure), but you get where I’m getting at.

Also, I have some difficulties understanding below:

The problem is, if I turn off the worryingly generous ‘public’ select
permission and try to add the SQLServerAgent user (which is the user
the agent is running under and the user that owns the agent job) and
give them select permissions, the job fails with this error message:

Please be very clear with the distinction of logins and user. Are you saying that you created a user in your database for the login that agent is using and granting that user SELECT permission isn’t enough? But what is weird is that you made that user the owner of the job!!? A user doesn’t own a job. A job is owned by a login. This is why we need to super-clear with how we phrase security related things regarding the distinction between a user and a login. So perhaps you meant to say that you make the login that Agent is using the owner of the job? How about making sa the job owner? Agent need to be sysadmin (as I said earlier), so it isn’t any difference from a security perspective who owns that job as long as it is a sysadmin. That is one troubleshooting measure to test.

Leave a Reply

Your email address will not be published. Required fields are marked *