0

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.

6
  • Could you add the error you get on the job history for the failing step, please? – Ronaldo Jan 13 at 13:42
  • Could you provide the detailed error message you're getting when the job fails? You generally shouldn't have to be altering the permissions of the public role, as it inherently has very minimal privileges and is a catch all for all Logins / Users for the underlying system. – J.D. Jan 13 at 13:47
  • Thanks. The error message is Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (error 22050). It only occurs when I remove 'select' permission from 'public'. This itself maybe the solution, there is nothing overly sensitive on the DB in question but it would be nice to know how to lock it down further. – Columbo Jan 13 at 14:01
  • Try adding the @append_query_error = 1 parameter to get a more detailed error message about the query. With that option you should receive the email with the error message related to the query instead of the job error. – Ronaldo Jan 13 at 14:08
  • I wasn't getting job fail emails which I now realise was because my mail profile in 'Properties->Alert System' was not enabled, it is now. Even with the append_query_error=1 setting the fail email provides no further information. But thanks for the information that is good to know. – Columbo Jan 13 at 14:23
0

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', 'domain\agentuser'

0

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];


USE LabDB;
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.

3
  • I checked this it seems emails can be sent, I can even trigger an stored procedure holding some simple SQL. But if I extend the SQL to include tables from other databases, it fails. – Columbo Jan 15 at 7:57
  • 1
    @Columbo, are the other databases on the same SQL Server instance? Are you using a linked server? Because a linked server can have specific accounts configured to access the remote databases. Since you're able to send the emails with a simple query, try isolating the more complex query you're having problems by running it alone on SSMS adding EXECUTE AS at the begin: EXECUTE AS LOGIN = 'TheLoginCausingTrouble';. With that you should get a clear message of what's the missing privilege. – Ronaldo Jan 15 at 10:25
  • 1
    Thanks Ronaldo, I've actually found that I need more control over the email I am sending (I need to send in preformatted Excel format) consequently I've moved over to .NET to do the send (scheduled off Task Manager). I will be coming back to get to the bottom of this when I have the time when I will try out your suggestion. In the mean time... thanks for your help, Thanks, – Columbo Jan 18 at 10:57
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.