1

I am running SQL Server 2022 Enterprise inside a company domain. I have a SQL Agent job that runs a stored procedure. The job runs under a domain account say 'domain\myuser' that is a member of 'sysadmin' on this sql server. It's also the same account that runs SQL Server Agent service.

When I manually run this job under SQL Server Agent in SSMS, I get this error whenever the @query parameter is supplied.

Executed as user: domain\myuser. Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050).

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'my_profile',           
    @recipients = '[email protected]',    
    @subject = 'Test Send',
    @query = 'SELECT * FROM msdb.dbo.backupfile WHERE 1=0';

If I simply remove @query, it works fine. If I include the @query but logged into SSMS as 'sa' and execute the SP in SSMS, it also works fine. It fails with that error when it's run as an agent job.

I have spent almost the entire day searching and reading but haven't been able to find a solution.

Does anyone have any idea what could be wrong or what other permissions might be needed?

Thanks

Improve this question
12
  • Can you run the query directly as domain\myuser? Commented Jul 30, 2024 at 6:43
  • Does domain\myuser have the sysadmin explicitly granted or is he a member of group which has sysadmin rights? Commented Jul 30, 2024 at 8:51
  • What if you change the job owner to sa? Also, what if you expicitly login to windows as the Agent account and run that code? Those two tests should give you further info for troubleshooting. Commented Jul 30, 2024 at 10:47
  • If I run the SP in SSMS logged in as 'domain\myuser', it fails with the same error. I already granted this 'domain\myuser' to be member of SYSADMIN. The job was created by 'sa' so the owner is 'sa'. I have already tried all those suggestions in above but none fixed the issue. This is why it's baffling to me. Does sp_send_dbmail need specific permissions? Commented Jul 30, 2024 at 13:48
  • What's the difference between the built-in 'sa' vs. creating a SQL login (ex: sa2) and granting 'sa2' to be a member of 'SYSADMIN'? For now, the only way I get it to work is the execute the SP as 'sa' in SSMS. If I execute it as 'sa2', it would fail with various errors. I can't remember exactly now since I was testing so many different things yesterday. I have a feeling 'sa' can do much more under the hood than 'sa2' in terms privileges. Commented Jul 30, 2024 at 13:55

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.